Conditional Aggregate Totals in SQL
I’ve been working on a SQL Server Reporting Services report which had to display several counts on each row. Each row represented a project and each project could have several tasks that were either ordinary tasks or milestones and could also be escalated. The report need to show something like this :
| Project Name | Milestone Counts | Task Count | Escalated Task Count | ||||||||
| 1st Week | 2nd Week | 3rd Week | >=4th Week | Total | 1st Week | 2nd Week | 3rd Week | >=4th Week | Total | ||
As you can see a task initially has to break down into two major colours of milestone and ordinary task counts but then is broken down into counts in the 1st, 2nd, 3rd and greater than 4th week totals including the total itself.
Initially this seemed tricky when thinking in terms of using count() but there’s an incredibly simple technique that solves this but which isn’t immediately obvious. The Sum() aggregate function can contain an expression and in this example that expression could compare the task date against late week dates and return either a 1 or 0 :
select ProjectName,
sum(case when IsMilestone = 1
and TaskDate < @LateDate
and Taskdate >@OneWeekLateDate
then 1 else 0 end) as Week1LateMilestoneCount
from Project
group by ProjectName
I’ve only included one example there but I think it’s enough to get the idea.
Post by: Philip Hendry