- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
What event source type are you running the report against? Which table?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
Cheers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
That's awesome. Where is this documented at / how did you come to know about this?
Thanks for posting it, I've got several use cases for this already.
ryan
