Calculate time between non back to back event dates

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.

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')

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.

Which version of SQL?  2008? 2012? Other?

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?

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.

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?

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.

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

Select allOpen in new window

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

Here is a report that does what you want.


mlmcc
LoggedIn.rpt
LoggedIN.xls

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

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

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

That should work. Thanks

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

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

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.

Here you go

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

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

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?

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

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

mlmcc

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

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

Thanks!

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?

Cool. Thanks!