62,046
社区成员
发帖
与我相关
我的任务
分享
declare @table table(加班员工id varchar(10),加班时间 datetime)
insert into @table values('1','2009-01-01')
insert into @table values('1','2009-01-02')
insert into @table values('1','2009-02-01')
insert into @table values('1','2009-03-01')
insert into @table values('1','2009-04-01')
insert into @table values('1','2009-05-01')
insert into @table values('1','2009-06-01')
insert into @table values('1','2009-07-01')
insert into @table values('1','2009-08-01')
insert into @table values('1','2009-09-01')
insert into @table values('1','2009-10-01')
insert into @table values('1','2009-11-01')
insert into @table values('1','2009-12-01')
insert into @table values('2','2009-12-01')
select
case month(加班时间) when 1 then '一月'
when 2 then '二月'
when 3 then '三月'
when 4 then '四月'
when 5 then '五月'
when 6 then '六月'
when 7 then '七月'
when 8 then '八月'
when 9 then '九月'
when 10 then '十月'
when 11 then '十一月'
when 12 then '十二月' end as 月份,
sum(1) as 加班次数
from @table
where 加班员工id='1'
group by month(加班时间)
--by Month
declare @time1 datetime, @time2 datetime
set @time1='2009-11-14'
set @time2='2009-11-14'
select convert(varchar(7),k.rn+@time1-1,120) as Date ,isnull(sum(GetMoney),0) as GetMoney
from AgentAmountLog a right join
(select ROW_NUMBER() over(order by getdate()) as rn from sys.columns a ,sys.columns b) k
on DATEDIFF(DAY,a.LogTime,k.rn+@time1-1)=0
where k.rn+@time1-1<=@time2
group by convert(varchar(7),k.rn+@time1-1,120)
order by Date