表1
dept fromdate enddate amount
a 2007-12-24 2007-12-28 11
b 2007-11-12 2007-11-25 22
v 2005-1-4 2005-2-4 33
统计结果显示如下:
dept 一月 二月 三月 四月.....十二月 amount
请问这个怎么做啊
dept fromdate enddate amount
a 2007-12-24 2007-12-28 11
b 2007-11-12 2007-11-25 22
v 2005-1-4 2005-2-4 33
统计结果显示如下:
dept 一月 二月 三月 四月.....十二月 amount
请问这个怎么做啊
b 2007-11-12 2007-11-25 22
v 2005-1-4 跨月的 amount 怎么解决
dept 一月 二月 三月 四月..... 十二月 total
a amount amount amount amount.... amount amountd的总和 也就是统计这个部门每个月的amount
[一月] = sum(case when datepart(mm,fromdate) = 1 then amount else 0 end),
.....from table1
group by dept
[一月] = sum(case when datepart(mm,fromdate) = 1 then amount else 0 end),
.....,
[ total ] = sum(amount)
from table1
group by dept
[一月] = sum(case when datepart(mm,fromdate) = 1 then amount else 0 end),
[二月] = sum(case when datepart(mm,fromdate) = 2 then amount else 0 end),
.....,
[十二月] = sum(case when datepart(mm,fromdate) = 12 then amount else 0 end),
[ total ] = sum(amount)
from table1
group by dept
insert into # values('b','2007-11-12','2007-11-25',22)
insert into # values('v','2005-1-4','2005-2-4',33)select dept,一月=sum(case when datepart(month,fromdate)=1 then amount else 0 end),
二月=sum(case when datepart(month,fromdate)=2 then amount else 0 end),
三月=sum(case when datepart(month,fromdate)=3 then amount else 0 end),
四月=sum(case when datepart(month,fromdate)=4 then amount else 0 end),
五月=sum(case when datepart(month,fromdate)=5 then amount else 0 end),
六月=sum(case when datepart(month,fromdate)=6 then amount else 0 end),
七月=sum(case when datepart(month,fromdate)=7 then amount else 0 end),
八月=sum(case when datepart(month,fromdate)=8 then amount else 0 end),
九月=sum(case when datepart(month,fromdate)=9 then amount else 0 end),
十月=sum(case when datepart(month,fromdate)=10 then amount else 0 end),
十一月=sum(case when datepart(month,fromdate)=11 then amount else 0 end),
十二月=sum(case when datepart(month,fromdate)=12 then amount else 0 end),
amount=sum(amount)
from # group by deptdept 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 amount
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
a 0 0 0 0 0 0 0 0 0 0 0 11 11
b 0 0 0 0 0 0 0 0 0 0 22 0 22
v 33 0 0 0 0 0 0 0 0 0 0 0 33(3 行受影响)
这个错误怎么处理啊
sum(case when datepart(mm,enddate )=2 then 1 else 0 end)as 二月,
sum(case when datepart(mm,enddate )=3 then 1 else 0 end) as 三月,
sum(case when datepart(mm,enddate )=4 then 1 else 0 end) as 四月,
sum(case when datepart(mm,enddate )=5 then 1 else 0 end) as 五月,
sum(case when datepart(mm,enddate )=6 then 1 else 0 end) as 六月,
sum(case when datepart(mm,enddate )=7 then 1 else 0 end) as 七月,
sum(case when datepart(mm,enddate )=8 then 1 else 0 end) as 八月,
sum(case when datepart(mm,enddate )=9 then 1 else 0 end) as 九月,
sum(case when datepart(mm,enddate )=10 then 1 else 0 end) as 十月,
sum(case when datepart(mm,enddate )=11 then 1 else 0 end) as 十一月,
sum(case when datepart(mm,enddate )=12 then 1 else 0 end) as 十二月
from table
group by dept
你的 amountd 是字符型的吧
你的 amountd 是字符型的吧
二月=sum(case when datepart(month,fromdate)=2 then cast(amount,float) else 0 end),
三月=sum(case when datepart(month,fromdate)=3 then cast(amount,float) else 0 end),
四月=sum(case when datepart(month,fromdate)=4 then cast(amount,float) else 0 end),
五月=sum(case when datepart(month,fromdate)=5 then cast(amount,float) else 0 end),
六月=sum(case when datepart(month,fromdate)=6 then cast(amount,float) else 0 end),
七月=sum(case when datepart(month,fromdate)=7 then cast(amount,float) else 0 end),
八月=sum(case when datepart(month,fromdate)=8 then cast(amount,float) else 0 end),
九月=sum(case when datepart(month,fromdate)=9 then cast(amount,float) else 0 end),
十月=sum(case when datepart(month,fromdate)=10 then cast(amount,float) else 0 end),
十一月=sum(case when datepart(month,fromdate)=11 then cast(amount,float) else 0 end),
十二月=sum(case when datepart(month,fromdate)=12 then cast(amount,float) else 0 end),
amount=sum(cast(amount,float))
from # group by dept
用这个试试
select dept,一月=sum(case when datepart(month,fromdate)=1 then cast(amount as float) else 0 end),
二月=sum(case when datepart(month,fromdate)=2 then cast(amount as float) else 0 end),
三月=sum(case when datepart(month,fromdate)=3 then cast(amount as float) else 0 end),
四月=sum(case when datepart(month,fromdate)=4 then cast(amount as float) else 0 end),
五月=sum(case when datepart(month,fromdate)=5 then cast(amount as float) else 0 end),
六月=sum(case when datepart(month,fromdate)=6 then cast(amount as float) else 0 end),
七月=sum(case when datepart(month,fromdate)=7 then cast(amount as float) else 0 end),
八月=sum(case when datepart(month,fromdate)=8 then cast(amount as float) else 0 end),
九月=sum(case when datepart(month,fromdate)=9 then cast(amount as float) else 0 end),
十月=sum(case when datepart(month,fromdate)=10 then cast(amount as float) else 0 end),
十一月=sum(case when datepart(month,fromdate)=11 then cast(amount as float) else 0 end),
十二月=sum(case when datepart(month,fromdate)=12 then cast(amount as float) else 0 end),
amount=sum(cast(amount as float))
from # group by dept
报这个错啊
二月=sum(case when datepart(month,fromdate)=2 then cast(amount as float) else 0 end),
三月=sum(case when datepart(month,fromdate)=3 then cast(amount as float) else 0 end),
四月=sum(case when datepart(month,fromdate)=4 then cast(amount as float) else 0 end),
五月=sum(case when datepart(month,fromdate)=5 then cast(amount as float) else 0 end),
六月=sum(case when datepart(month,fromdate)=6 then cast(amount as float) else 0 end),
七月=sum(case when datepart(month,fromdate)=7 then cast(amount as float) else 0 end),
八月=sum(case when datepart(month,fromdate)=8 then cast(amount as float) else 0 end),
九月=sum(case when datepart(month,fromdate)=9 then cast(amount as float) else 0 end),
十月=sum(case when datepart(month,fromdate)=10 then cast(amount as float) else 0 end),
十一月=sum(case when datepart(month,fromdate)=11 then cast(amount as float) else 0 end),
十二月=sum(case when datepart(month,fromdate)=12 then cast(amount as float) else 0 end),
amount=sum(cast(amount as float))
from # group by dept
select dept,一月=cast(sum(case when datepart(month,fromdate)=1 then cast(amount as float) else 0 end) as decimal(38,2)),
二月=cast(sum(case when datepart(month,fromdate)=2 then cast(amount as float) else 0 end) as decimal(38,2)),
三月=cast(sum(case when datepart(month,fromdate)=3 then cast(amount as float) else 0 end) as decimal(38,2)),
四月=cast(sum(case when datepart(month,fromdate)=4 then cast(amount as float) else 0 end) as decimal(38,2)),
五月=cast(sum(case when datepart(month,fromdate)=5 then cast(amount as float) else 0 end) as decimal(38,2)),
六月=cast(sum(case when datepart(month,fromdate)=6 then cast(amount as float) else 0 end) as decimal(38,2)),
七月=cast(sum(case when datepart(month,fromdate)=7 then cast(amount as float) else 0 end) as decimal(38,2)),
八月=cast(sum(case when datepart(month,fromdate)=8 then cast(amount as float) else 0 end) as decimal(38,2)),
九月=cast(sum(case when datepart(month,fromdate)=9 then cast(amount as float) else 0 end) as decimal(38,2)),
十月=cast(sum(case when datepart(month,fromdate)=10 then cast(amount as float) else 0 end) as decimal(38,2)),
十一月=cast(sum(case when datepart(month,fromdate)=11 then cast(amount as float) else 0 end) as decimal(38,2)),
十二月=cast(sum(case when datepart(month,fromdate)=12 then cast(amount as float) else 0 end) as decimal(38,2)),
amount=cast(sum(cast(amount as float)) as decimal(38,2))
from # group by dept
dept 一月 二月.....
a 11 22
b 22 10
total 33 32
上面的效果
dept 一月 二月.....
a 11 22
b 22 10
total 33 32
上面的效果
select dept,一月=cast(sum(case when datepart(month,fromdate)=1 then cast(amount as float) else 0 end) as decimal(38,2)),
二月=cast(sum(case when datepart(month,fromdate)=2 then cast(amount as float) else 0 end) as decimal(38,2)),
三月=cast(sum(case when datepart(month,fromdate)=3 then cast(amount as float) else 0 end) as decimal(38,2)),
四月=cast(sum(case when datepart(month,fromdate)=4 then cast(amount as float) else 0 end) as decimal(38,2)),
五月=cast(sum(case when datepart(month,fromdate)=5 then cast(amount as float) else 0 end) as decimal(38,2)),
六月=cast(sum(case when datepart(month,fromdate)=6 then cast(amount as float) else 0 end) as decimal(38,2)),
七月=cast(sum(case when datepart(month,fromdate)=7 then cast(amount as float) else 0 end) as decimal(38,2)),
八月=cast(sum(case when datepart(month,fromdate)=8 then cast(amount as float) else 0 end) as decimal(38,2)),
九月=cast(sum(case when datepart(month,fromdate)=9 then cast(amount as float) else 0 end) as decimal(38,2)),
十月=cast(sum(case when datepart(month,fromdate)=10 then cast(amount as float) else 0 end) as decimal(38,2)),
十一月=cast(sum(case when datepart(month,fromdate)=11 then cast(amount as float) else 0 end) as decimal(38,2)),
十二月=cast(sum(case when datepart(month,fromdate)=12 then cast(amount as float) else 0 end) as decimal(38,2)),
amount=cast(sum(cast(amount as float)) as decimal(38,2))
from # group by dept
union all
select 'total',一月=cast(sum(case when datepart(month,fromdate)=1 then cast(amount as float) else 0 end) as decimal(38,2)),
二月=cast(sum(case when datepart(month,fromdate)=2 then cast(amount as float) else 0 end) as decimal(38,2)),
三月=cast(sum(case when datepart(month,fromdate)=3 then cast(amount as float) else 0 end) as decimal(38,2)),
四月=cast(sum(case when datepart(month,fromdate)=4 then cast(amount as float) else 0 end) as decimal(38,2)),
五月=cast(sum(case when datepart(month,fromdate)=5 then cast(amount as float) else 0 end) as decimal(38,2)),
六月=cast(sum(case when datepart(month,fromdate)=6 then cast(amount as float) else 0 end) as decimal(38,2)),
七月=cast(sum(case when datepart(month,fromdate)=7 then cast(amount as float) else 0 end) as decimal(38,2)),
八月=cast(sum(case when datepart(month,fromdate)=8 then cast(amount as float) else 0 end) as decimal(38,2)),
九月=cast(sum(case when datepart(month,fromdate)=9 then cast(amount as float) else 0 end) as decimal(38,2)),
十月=cast(sum(case when datepart(month,fromdate)=10 then cast(amount as float) else 0 end) as decimal(38,2)),
十一月=cast(sum(case when datepart(month,fromdate)=11 then cast(amount as float) else 0 end) as decimal(38,2)),
十二月=cast(sum(case when datepart(month,fromdate)=12 then cast(amount as float) else 0 end) as decimal(38,2)),
amount=cast(sum(cast(amount as float)) as decimal(38,2))
from # dept 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 amount
---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
a 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 11.00
b 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 22.00 0.00 22.00
v 33.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 33.00
total 33.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 22.00 11.00 66.00(4 行受影响)