Friday, August 8, 2008

Using SUM(CASE) and SQL Date Functions to compute YTD Totals

I needed to generate a report that had your typical YTD, MTD, Current Week, etc. data. This seemed like a perfect time for a SUM(CASE) statement:


SELECT     'Number of Vouchers Processed' AS Query,
SUM(CASE WHEN Form$Status IN ('Completed', 'Rejected') AND
End$Dt <= @EndDt AND End$Dt >= DATEADD(dd, -7, @EndDt)
THEN 1 ELSE 0 END ) AS WeekCount,
SUM(CASE WHEN Form$Status IN ('Completed', 'Rejected') AND
Month(End$Dt) = Month(@EndDt)
THEN 1 ELSE 0 END ) AS MonthCount,
SUM(CASE WHEN Form$Status IN ('Completed', 'Rejected') AND
Year(End$Dt) = Year(@EndDt)
THEN 1 ELSE 0 END ) AS YearCount,
SUM(CASE WHEN Form$Status IN ('Completed', 'Rejected')
THEN 1 ELSE 0 END ) AS TotalCount
FROM bam_TV_Completed AS TRC

No comments: