我需要按月,县和程序生成支出清单和申请数量。到目前为止,我已经能够获得支出清单,但是我很难获得每月申请数量的清单。这是我到目前为止的查询,但申请数量不正确。
select
servicecounty AS County,
program,
sum(case when month(entrydate) = 1 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as January,
sum(case when month(entrydate) = 2 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as February,
sum(case when month(entrydate) = 3 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as March,
sum(case when month(entrydate) = 4 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as April,
sum(case when month(entrydate) = 5 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as May,
sum(case when month(entrydate) = 6 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as June,
sum(case when month(entrydate) = 7 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as July,
sum(case when month(entrydate) = 8 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as August,
sum(case when month(entrydate) = 9 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as September,
sum(case when month(entrydate) = 10 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as October,
sum(case when month(entrydate) = 11 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as November,
sum(case when month(entrydate) = 12 and year(entrydate) = 2014 then totalpaymenttotal else 0 end) as December,
sum(case when month(entrydate) = 1 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [January 15],
sum(case when month(entrydate) = 2 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [February 15],
sum(case when month(entrydate) = 3 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [March 15],
sum(case when month(entrydate) = 4 and year(entrydate) = 2015 then totalpaymenttotal else 0 end) as [April 15]
from Sheet$
group by servicecounty, program
UNION ALL
select
servicecounty AS County,
program,
COUNT(case when month(entrydate) = 1 and year(entrydate) = 2014 then ApplicationID else 0 end) as January,
count(case when month(entrydate) = 2 and year(entrydate) = 2014 then ApplicationID else 0 end) as February,
count(case when month(entrydate) = 3 and year(entrydate) = 2014 then ApplicationID else 0 end) as March,
count(case when month(entrydate) = 4 and year(entrydate) = 2014 then ApplicationID else 0 end) as April,
count(case when month(entrydate) = 5 and year(entrydate) = 2014 then ApplicationID else 0 end) as May,
count(case when month(entrydate) = 6 and year(entrydate) = 2014 then ApplicationID else 0 end) as June,
count(case when month(entrydate) = 7 and year(entrydate) = 2014 then ApplicationID else 0 end) as July,
count(case when month(entrydate) = 8 and year(entrydate) = 2014 then ApplicationID else 0 end) as August,
count(case when month(entrydate) = 9 and year(entrydate) = 2014 then ApplicationID else 0 end) as September,
count(case when month(entrydate) = 10 and year(entrydate) = 2014 then ApplicationID else 0 end) as October,
count(case when month(entrydate) = 11 and year(entrydate) = 2014 then ApplicationID else 0 end) as November,
count(case when month(entrydate) = 12 and year(entrydate) = 2014 then ApplicationID else 0 end) as December,
Count(case when month(entrydate) = 1 and year(entrydate) = 2015 then ApplicationID else 0 end) as [January 15],
Count(case when month(entrydate) = 2 and year(entrydate) = 2015 then ApplicationID else 0 end) as [February 15],
Count(case when month(entrydate) = 3 and year(entrydate) = 2015 then ApplicationID else 0 end) as [March 15],
Count(case when month(entrydate) = 4 and year(entrydate) = 2015 then ApplicationID else 0 end) as [April 15]
from Sheet$
group by servicecounty, program
ORDER BY program
该报告应如下所示:
按月,计划和县划分的支出:
http://i.stack.imgur.com/yJ26A.jpg
按月,程序和县划分的应用数量:
http://i.stack.imgur.com/7Aqkk.png
该表包含以下字段:ServiceCounty,TotalPaymentTotal,Program,ApplicationID,EntryDate
先谢谢您的帮助。
最佳答案
您在第二个子查询中的计数都返回相同的值。请记住,count()
计算非NULL值的数量。并且,0
不为空。
三种解决方案:
将count()
更改为sum()
,将then
更改为then 1
。
删除else
。
将else 0
更改为else NULL
。
这些是我个人喜好的顺序。