Any way to narrow report by TIME range?
I am interested in creating a report that shows the user activity between 10 AM and 2 PM every day for 8 months. I would like to have this show in as few reports as possible. I cannot seem to figure out how to do this without having to run individual reports for each day or creating a SQL statement that uses Date/Time >= and <= (but again, it is by day, so several OR statements for each day).
Of course I can run the report by month and then manually trim out the data not needed, but that's a PITA...
Has anyone conquered this problem?
My recommendation would be to use a SQL function in the where clause. Specifically use datename which takes two parameters (Date component, SQL Column name).
Here are the available date components you can use:
year yy 1753-9999
quarter qq 1-4
month mm 1-12
week wk 1-54
day dd 1-31
dayofyear dy 1-366
weekday dw 1- 7 (Sunday-Saturday)
hour hh 0-23
minute mi 0-59
second ss 0-59
So, assuming you were using the WindowDeviceStamp column for the date data you might format your where clause like this:
datename(hh, WindowDeviceStamp) >= 10 AND datename(hh, WindowDeviceStamp) < 2