SQL Tips n Tricks: Show events by time of day

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:

Num Files Hour Of Day Monday Tuesday Wednesday Thursday Friday Saturday Sunday
23 6:00 6 2 4 7 2 1 1
80 6:30 12 17 19 17 10 5 0
616 7:00 86 132 129 137 115 17 0
1006 7:30 130 214 183 239 177 63 0
1963 8:00 285 374 449 390 389 76 0
2613 8:30 391 523 557 488 547 107 0
3529 9:00 588 750 690 744 648 109 0
3670 9:30 692 787 701 701 663 126 0
4214 10:00 699 944 851 847 738 133 2
4753 10:30 835 1004 902 955 938 118 1
4850 11:00 885 976 1019 917 954 96 3
4549 11:30 826 920 913 927 883 76 4
4504 12:00 821 866 869 1077 796 72 3
3792 12:30 694 757 793 765 705 75 3
4170 13:00 738 898 838 874 776 42 4
4333 13:30 908 891 868 814 801 49 2
4234 14:00 856 866 774 880 800 54 4
3948 14:30 733 833 787 788 767 36 4
4721 15:00 860 930 1121 890 882 36 2
5089 15:30 1111 1013 977 985 967 30 6
4773 16:00 962 974 937 874 1005 20 1
4753 16:30 944 971 986 865 947 38 2
4516 17:00 958 932 908 855 844 14 5
3519 17:30 716 727 696 687 679 13 1
2761 18:00 497 558 560 600 538 6 2
2129 18:30 447 446 424 432 377 3 0
852 19:00 183 180 184 150 151 4 0
53 19:30 9 8 15 7 8 6 0
24 20:00 9 1 8 0 2 1 3
6 20:30 0 0 0 0 1 0 5
19 21:00 2 4 4 6 2 0 1
4 21:30 1 0 1 0 0 0 2

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 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s