Handling 'fiscal days' that don't end at midnight? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Handling 'fiscal days' that don't end at midnight?

Author

Topic

figmo
Starting Member

18 Posts

Posted - 2012-01-02 : 10:19:27

I have a table that tracks usage events for a customer. Each time they login or logout of our application, an event is saved to the table. I need to generate a report that shows what days the customer used the application and the length of time (in minutes) it was used.

Here are the catches:

1. Our "fiscal day" does not end at midnight. It ends at 4:00 AM. So events that occur between 4:00 AM and 3:59 AM the next day are all considered to be on the same date.

2. There can be multiple logins/logouts during the day. The length of time used needs to show the length of time span from the first event for that day and the last event of that day. So it is not adding up the spans between each login/logout. Only the total between first login and last logout for that "fiscal day".

TABLE: UsageEvents
CustName VARCHAR(20)
Event VARCHAR(10)
EventDate DATETIME

Sample Data:
'Customer1, 'Login', '12/15/2011 21:00:00'
'Customer1, 'Logout', '12/15/2011 23:10:00'
'Customer1, 'Login', '12/15/2011 23:18:00'
'Customer1, 'Logout', '12/16/2011 2:25:00'
'Customer2, 'Login', '12/15/2011 21:00:00'
'Customer2, 'Logout', '12/15/2011 23:10:00'

And the output I need to get is:
'Customer1', '12/15/2011', 325 <--# mins used
'Customer2', '12/15/2011', 130 <--# mins used

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-02 : 10:43:54

Instead of grouping by date, you can move the date by four hours and do the grouping - sort of like this:

SELECT
   DATEDIFF(minute,
      MIN(CASE WHEN [Event] = 'Login' THEN EventDate END),
      MAX(CASE WHEN [Event] = 'Logout' THEN EventDate END)),
   CustName,
   CAST(DATEADD(hour,4,EventDate) AS DATE)
FROM
   dbo.UsageEvents
GROUP BY
   CustName,
   CAST(DATEADD(hour,4,EventDate) AS DATE);

But, if there is the possibility that someone may log on at 2300 Hrs on a given day and then log out at 0600 Hrs on the following day, query above will not handle that. You did not ask for that, so I won't imagine and try to solve a problem where there is none

figmo
Starting Member

18 Posts

Posted - 2012-01-02 : 13:40:14

good idea. in fact, it was a GREAT idea. It worked like a charm. Thank you!

FYI - the only change I made was in the column selection. Changed

CAST(DATEADD(hour,4,EventDate) AS DATE)
to
CAST(MIN(EventDate) AS DATE)

This ensures that the date reported is the start of the date range, not the end. So if they login on the 22nd at noon, and logout on the 23rd at 2AM - the date reported is the 22nd.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources