SELECT IsConfirmed, IsNetConfirmed, d.FullDate FROM Final.FactApplication f
INNER JOIN final.DimOfferedDate d on f.OfferedDateKey= d.OfferedDateKey
WHERE d.CalendarYear in ('2013','2014','2015')
上面的代码返回以下示例数据。
IsConfirmed IsNetConfirmed FullDate
----------------------------------------------
1 0 2013-01-04 00:00:00.000
1 1 2013-02-04 00:00:00.000
0 1 2013-03-04 00:00:00.000
1 0 2013-04-04 00:00:00.000
我希望能够对每年的IsConfirmed和IsNetConfirmed进行按日和月份汇总,以便得出以下结果。我需要对每年进行聚合,以便4/31/2012的总和包括1/1/2012-4/31/2012之间的数据。
![enter image description here](https://istack.dev59.com/VDz4w.webp)
SELECT sum(IsConfirmed) AS ConfirmCount
,sum(IsNetConfirmed) AS NetConfirmCount
,year(d.fulldate) AS cyear
,month(d.fulldate) AS cmonth
,day(d.fulldate) AS cday
FROM final.FactApplicationHistory f
INNER JOIN final.DimOfferedDate d ON f.OfferedDateKey = d.OfferedDateKey
WHERE d.CalendarYear IN ('2013','2014','2015')
GROUP BY year(d.fulldate)
,month(d.fulldate)
,day(d.fulldate)
ORDER BY year(d.fulldate)
sum(case when year(fulldate) = 2012 then IsConfirmed else 0 end) as 2012Confirmed
。 - Andrew