Calculate time between non back to back event dates

Greg Watkins
Greg Watkins used Ask the Experts™
on
I have a SQL table that contains a series of events that are in chronological order that I need to run a crystal report on that will calculate and then total the time between specific events.  The table fields that are used are the event date, event name. The problem is that the two events that I need to get the total time for may not be back to back events. Other events may happen in between the two events  For example. A sample of these events might be as follows.

2019-03-01 10:00:00 Logged In
2019-03-01 10:03:00 Paused
2019-03-01 10:05:00 Un-Paused
2019-03-01 10:05:10 Called
2019-03-01 10:05:10 Completed
2019-03-01 10:08:00 Logged Out
2019-03-01 10:09:10 Connected
2019-03-01 10:15:10 Complected
2019-03-01 10:21:00 Logged In
2019-03-01 10:30:00 Logged Out

I need to calculate the total Logged In Time based upon when the agent Logged In and Logged Out, which in this example was twice but there was a period of time between the first logout and the second login when the agent was not logged in. In the example above the Total Logged In Time would be 17 minutes. Basically when I find a Logged In Event then I need to find the next Logged Out event and use the date and time difference to get the duration but how do I find the next Logged Out event?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
If there are no missing Logins/Logouts, then it's very easy, and you don't even need to match rows up:

SELECT SUM(DATEDIFF(MINUTE, 0, event_date) * CASE WHEN event_name = 'Logged Out' THEN 1 ELSE -1 END) AS Total_Minutes_Logged_In
FROM dbo.table_name
WHERE event_name IN ('Logged In', 'Logged Out')

Should be very close at least, although I'll have to create usable test data to verify, but that's the general pattern.

Of course we could a "GROUP BY employee_id" to handle multiple employees at the same time.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Here's usable sample data for anyone that needs it.  Btw, this is a great way to post data, which makes people more likely to work on your q :-).

CREATE TABLE #events (
    event_date datetime NOT NULL,
    event_name varchar(30) NOT NULL
    )
INSERT INTO #events VALUES
('2019-03-01 10:00:00','Logged In'),
('2019-03-01 10:03:00','Paused'),
('2019-03-01 10:05:00','Un-Paused'),
('2019-03-01 10:05:10','Called'),
('2019-03-01 10:05:10','Completed'),
('2019-03-01 10:08:00','Logged Out'),
('2019-03-01 10:09:10','Connected'),
('2019-03-01 10:15:10','Complected'),
('2019-03-01 10:21:00','Logged In'),
('2019-03-01 10:30:00','Logged Out')

Author

Commented:
Thanks Scott. I will give that a shot.

Since the user can define the date and time range that the report uses as part of it's selection parameters it is possible that the first Logged In or the Last Logged out events would not be included in the records. Any ideas on how to handle that situation?

FYI. I am already grouping the report by a field similar to the employee ID so that part should already be taken care of.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Which version of SQL?  2008? 2012? Other?

Author

Commented:
SQL 2014

My other question is this is a standard SQL query. How do I use that in Crystal reports?  I think I need a similar Crystal reports formula that I can use within the report right?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I suppose so.  I have no idea on how to write Crystal formulas, although when I last used Crystal (many years ago, thankfully), you could write you own native SQL query and have those results sent to Crystal.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Since the user can define the date and time range that the report uses as part of it's selection parameters it is possible that the first Logged In or the Last Logged out events would not be included in the records. Any ideas on how to handle that situation?

Would it be acceptable to retrieve the login time if the logout is within the window?

If the login is within the window and the logout is not, wouldn't it make sense to ignore that login, since it will extend beyond the time window?

Author

Commented:
Since the user has defined what date and time range they are concerned with then I think it would make more sense to simply use the first event date as the first logged in date and time if the first Logged in event is missing and/or use the last event date as the last Logged Out event date and time if it is missing. That way the user would get the total logged in time strictly within the time frame they selected.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
OK, that makes sense too.

/* create test login/logout data */
IF OBJECT_ID('tempdb.dbo.#events') IS NOT NULL
    DROP TABLE #events;
CREATE TABLE #events (
    emp_id int NOT NULL,
    event_date datetime NOT NULL,
    event_name varchar(30) NOT NULL
    )
TRUNCATE TABLE #events
INSERT INTO #events VALUES
(10, '2019-03-01 10:00:00','Logged In'),
(10, '2019-03-01 10:03:00','Paused'),
(10, '2019-03-01 10:05:00','Un-Paused'),
(10, '2019-03-01 10:05:10','Called'),
(10, '2019-03-01 10:05:10','Completed'),
(10, '2019-03-01 10:08:00','Logged Out'),
(10, '2019-03-01 10:09:10','Connected'),
(10, '2019-03-01 10:15:10','Complected'),
(10, '2019-03-01 10:21:00','Logged In'),
(10, '2019-03-01 10:30:00','Logged Out'),
(20, '2019-03-01 10:01:00','Logged In'),
(20, '2019-03-01 10:11:00','Logged Out'),
(20, '2019-03-01 10:14:00','Logged In'),
(30, '2019-03-01 10:30:00','Logged Out')

/* do actual time calcs */

DECLARE @start_time datetime
DECLARE @end_time datetime
SET @start_time = '2019-03-01 10:00:00'
SET @end_time = '2019-03-01 10:30:00'

SELECT
    emp_id,
    logout_value - login_value + CASE
        WHEN login_count = logout_count THEN 0
        WHEN login_count > logout_count THEN DATEDIFF(MINUTE, 0, @end_time)
        ELSE -DATEDIFF(MINUTE, 0, @start_time) END AS net_minutes
FROM (
    SELECT
        emp_id,
        SUM(CASE WHEN event_name = 'Logged In' THEN DATEDIFF(MINUTE, 0, event_date) ELSE 0 END)
            AS login_value,
        SUM(CASE WHEN event_name = 'Logged In' THEN 1 ELSE 0 END) AS login_count,
        MAX(CASE WHEN event_name = 'Logged In' THEN event_date END) AS login_last,
        SUM(CASE WHEN event_name = 'Logged Out' THEN DATEDIFF(MINUTE, 0, event_date) ELSE 0 END)
            AS logout_value,
        SUM(CASE WHEN event_name = 'Logged Out' THEN 1 ELSE 0 END) AS logout_count,
        MAX(CASE WHEN event_name = 'Logged Out' THEN event_date END) AS logout_last
    FROM #events
    WHERE event_name IN ('Logged In', 'Logged Out')
    GROUP BY emp_id
) AS query1
I've placed date-criteria in the first CTE, this may cause a missing Logged In or Logged Out event.  I've numbered the rows by Event Date, and I've captured MIN(Logged In) and MAX(Logged Out).  The MIN/MAX ensures matching Logged In/Out pairs:
WITH
LoggedCTE AS
(	SELECT	*
	,	rownumber = ROW_NUMBER() OVER (ORDER BY event_date)
	FROM	[events]
	WHERE	event_name in ('Logged In','Logged Out')
	AND	event_date BETWEEN '2019-03-01 10:00:00' AND '2019-03-01 10:30:00'
),
MinMaxLoggedCTE AS
(	SELECT	rownumberMIN	= MIN(CASE WHEN event_name = 'Logged In'  THEN rownumber END)
	,	rownumberMAX	= MAX(CASE WHEN event_name = 'Logged Out' THEN rownumber END)
	FROM	LoggedCTE
)
SELECT 	Total_Minutes_Logged_In = SUM(DATEDIFF(MINUTE, 0, a.event_date) * CASE WHEN a.event_name = 'Logged Out' THEN 1 ELSE -1 END)
FROM	LoggedCTE a
CROSS JOIN	MinMaxLoggedCTE b
WHERE 	a.rownumber BETWEEN b.rownumberMIN AND b.rownumberMAX

Open in new window

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
IN the report are you doing anything with the details of each record or are simply trying to group on the user and find out how long they were logged in the given timeframe?

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Here is a report that does what you want.

Report picture
mlmcc
LoggedIn.rpt
LoggedIN.xls

Author

Commented:
Thanks mlmcc. I think that will work. I will give it a shot.

Author

Commented:
Ok mlmcc. Your formulas worked great but I have one other request. In the details section of the report I also need to show the duration of each individual "Logged In" event which would basically be the time between the logged in event and the next logged out event, if there is one. So the report details would look something like this....

      Event Date                      Event                 Duration
        3/1/2019  10:00:00AM       Logged In          08:00:00
      3/1/2019  10:03:00AM       Paused      
      3/1/2019  10:05:00AM       Un Paused      
      3/1/2019  10:05:10AM       Completed      
      3/1/2019  10:05:10AM       Called      
      3/1/2019  10:08:00AM       Logged Out      
      3/1/2019  10:09:10AM       Connected      
      3/1/2019  10:15:10AM       Complected      
      3/1/2019  10:21:00AM       Logged In           09:00:00
      3/1/2019  10:30:00AM       Logged Out
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Does it have to be on the logged in row or can it be on the logged out row?

The value isn't calculated until the logged out record is processed.

If that will work use this report
LoggedIn.rpt

Author

Commented:
That should work. Thanks

Author

Commented:
mlmcc, I think I am getting close but still having a little problem. I took your sample formulas and used them to create two of my own (Declare Variables, EventTime). The idea is that these formulas would not only get the Logged In event info but would also calculate the same info for all of the other possible events. But I am getting an error in the DeclareVariables formula that I can't figure out. The error says a date is required on the last variable but that value is a date so it must something in the syntax. I am attaching the report along with a csv file with the sample data I am testing with. As you will see each start event has a corresponding stop event. i.e Logged In-Logged Out, Paused - UnPaused etc... My goal is be able to calculate the total time between the start and stop events and show a summary of each of those times and also show the duration of the event next to the corresponding stop event in the details.

Your help is greatly appreciated.
queue_member_events.csv
Agent_Status_Details.rpt
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The declare variables problem is some of the IF-THEN-ELSE parts last line sets a date value and others set a Boolean.

Since that formula part duplicates your Event_time it really isn't needed, you only need the declarations and resetting of values.

I fixed it by commenting it out.

Also your data has a problem.  The last unpaused doesn't have a pause event before it.
You probably need to set Paused to TRUE when the pause event is found.

mlmcc
Agent_Status_Details.rpt
queue_member_events.xls

Author

Commented:
Hi mlmcc. That worked great.  I have one more formula that I need your help with... I need a formula that will calculate the agents total Available Time which would be the difference between the total "Logged In" time minus the sum of total duration of the "Paused", "Connected", "Inbound Call" and "Outbound Call" events.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Here you go

You just need another formula to calculate using the total times for all variables
Agent_Status_Details-rev2.rpt

Author

Commented:
Thanks mlmcc. I was already going down that road. That worked perfectly. I have two more requests from the user.

One request is to show if the user was already logged in at the start of the report time frame like you do with the end of the time frame. The way to know that would be if the first event was anything other than "Logged In".

The second request is related to the Cross-tab that counts the sum of each reason that is associated with the Paused event. The user wants to also see the total duration of the paused event for each unique reason. That would be the difference between the Paused event and the next Un-Paused event but I would need a formula that would calculate the total duration of each unique paused event reason.

I am uploading the latest version of my report and sample data with more reasons.
queue_member_events.csv
Agent_Status_Details.rpt

Author

Commented:
Actually I have ran into a little issue with the formula that calculates the total times for the events. The user can define the start and end times for the report which means that the last event could basically be anything and it could also happen anytime within the report time frame. Each event has a corresponding "end" event which is used to determine the duration. The problem this presents is due to the fact that the formula uses the report end time for some of it's calculations if there isn't any corresponding "end" event. For example, I had a scenario where the last three user events were Logged In, Paused(after about 30 seconds) and then Logged out. The duration between the Logged In and Logged Out events was about 2 minutes but the last event date (Logged out) was about 5 minutes before the report end date. This caused the Total Paused Time to be calculated from the Paused event date up to the report end time. As a result the Total Paused time was higher than the Total Logged In time which made the total available time a minus value. It really should have been 1:30. I think the only way to handle this type of scenario would be to have the total duration of each event if there is no corresponding end event to be calculated based upon the date of the last event instead of the report end time. Any ideas?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Quickly off the top of my head I don't.  

If he logs off you want all other event time to end?

mlmcc

Author

Commented:
I think I have that issue resolved. The formulas I was using to calculate the totals was trying to set the event date to the end of the report time. I used your formulas for the totals instead and now it calculates the totals correctly.  

But I have ran into another weird problem.... when the report goes to the next page the event total time variables are getting set back to 0 if that event does not show up in the details of the next page. My DeclareVariables formula is in the Group Header so they should not be getting reset back to 0 until the group changes. Any idea why that would happen? I am attaching the latest version. It has a new report name.

Thanks
Agent_Availability_Details.rpt
queue_member_events.csv

Author

Commented:
Any ideas why my variables are getting cleared when there are no matching values on the next page? I could really use your help on this one.

Thanks
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
The group header is being repeated on every page so the formulas get executed.  Change it to

If Not InRepeatedGroupHeader Then
(
    TimeInSecondsCurrentLogIn := 0;
    TimeInSecondsTotalLogIn := 0;
    TimeInSecondsCurrentPaused := 0;
    TimeInSecondsTotalPaused := 0;
    TimeInSecondsCurrentConnected := 0;
    TimeInSecondsTotalConnected := 0;
    TimeInSecondsCurrentInboundCall := 0;
    TimeInSecondsTotalInboundCall := 0;
    TimeInSecondsCurrentOutboundCall := 0;
    TimeInSecondsTotalOutboundCall := 0;
    TimeInSecondsCurrentHold := 0;
    TimeInSecondsTotalHold := 0;
    ReportStartTime :=  Minimum({?DateTime});
);
'';

Open in new window


I updated the report

mlmcc
Agent_Availability_Details.rpt

Author

Commented:
Thanks!

Author

Commented:
I have one other little issue with the formulas for the logged in and logged out events where it converts the eventhours, eventminutes and event seconds to a date format. Sometimes the total logged in hours can be more than 23 and when that happens I get an error that the eventhours must be between 0 and 23. How can I handle that?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Change each of the formulas that display the times to include calculating the days

WhilePrintingRecords;
Global NumberVar TimeInSecondsTotalLogIn;
Local StringVar strOut;

//
//  Add this line
Local NumberVar EventDays;

Local NumberVar EventHours;
Local NumberVar EventMinutes;
Local NumberVar EventSeconds;

strOut := '';

EventSeconds := TimeInSecondsTotalLogIn;

//
//  Add these lines
EventDays := EventSeconds \ 86400;
EventSeconds := EventSeconds mod 86400;

EventHours := EventSeconds \ 3600;
EventSeconds := EventSeconds mod 3600;
EventMinutes := EventSeconds \ 60;
EventSeconds := EventSeconds mod 60;
strOut := CStr(DateTime(1900,1,1,EventHours,EventMinutes,EventSeconds), 'HH:mm:ss');

//
//  Add this test
If EventDays > 0 then
    strOut := CStr(EventDays, 0) & ' days and ' & strOut;

strOut;

Open in new window

Author

Commented:
Cool. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial