Sometime times I get a request for data from a user that is actually fun to write.  Today was one like that.  The request from the user was to look at a series of event that occurred in the last week.  It needed to be organized by day of week and time of day, by the half hour.  Now that it is done I see a series of user reports in my future that takes this data and presents it for user review.

Here is what the data looks like after it is run:

Here is the SQL used to create it:

SELECT COUNT(Audit_Row_Id) AS NumberFilesRun
     , RIGHT('00' + CAST(DATEPART(HOUR, FI.Audit_Create_Date) AS NVARCHAR(2)), 2) 
       + ':' 
       + (CASE WHEN DATEPART(MINUTE, FI.Audit_Create_Date) < 30 THEN '00' ELSE '30' END) 
       AS HourOfDay
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Monday' THEN 1 ELSE 0 END) AS Monday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Tuesday' THEN 1 ELSE 0 END) AS Tuesday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Wednesday' THEN 1 ELSE 0 END) AS Wednesday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Thursday' THEN 1 ELSE 0 END) AS Thursday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Friday' THEN 1 ELSE 0 END) AS Friday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Saturday' THEN 1 ELSE 0 END) AS Saturday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Sunday' THEN 1 ELSE 0 END) AS Sunday
FROM Audit_File_Information FI
WHERE Audit_Create_User = 'UniqueUserName'
AND Audit_Create_Date BETWEEN DATEADD(week, -2, GETDATE()) AND  DATEADD(week, -1, GETDATE())
GROUP BY RIGHT('00' 
             + CAST(DATEPART(HOUR, FI.Audit_Create_Date) AS NVARCHAR(2)), 2) 
             + ':' 
			 + (CASE WHEN DATEPART(MINUTE, FI.Audit_Create_Date) < 30 THEN '00' ELSE '30' END)
ORDER BY 2