I am trying to evaluate how long a user is potentially online for.

Below is the data structure I am working with.

I am using the following logic:

• When the Log-On time is not provided, use the Log-Off time.
• When the Log-Off time is not provided, use the Log-On time.
• I am taking the MIN LogOn time for a particular day.
• I am taking the MAX LogOff time for a particular day.

Below is the expected Outcome:

Below is a query that I having been trying, but with no luck yet:

SELECT      [Name]
            ,[Date]
            ,ISNULL(LogOn, MIN(CASE WHEN [Event] IN ('Log-Off') THEN [Time] ELSE NULL END)) AS MinLogOnTime
            ,ISNULL(LogOff, MAX(CASE WHEN [Event] IN ('Log-On') THEN [Time] ELSE NULL END)) AS MaxLogOffTime
            ,DATEDIFF(HH, ISNULL(LogOff, MAX(CASE WHEN [Event] IN ('Log-Off') THEN [Time] ELSE NULL END)), ISNULL(LogOn, MIN(CASE WHEN [Event] IN ('Log-On') THEN [Time] ELSE NULL END)) AS 'TimeDifferenceHr ( Max - Min Time)'

FROM        (
            SELECT          [Name]
                            ,[Date]
                            ,[Event]
                            ,MIN(CASE WHEN [Event] IN ('Log-On') THEN [Time] ELSE NULL END) AS LogOn 
                            ,MAX(CASE WHEN [Event] IN ('Log-Off') THEN [Time] ELSE NULL END) AS LogOff 
            GROUP BY        [Name], [Date], [Event]
            ) x
GROUP BY    [Name], [Date]
ORDER BY    [Name], [Date]

I would really appreciate any assistance with this.

CodePudding user response：

based on the comments, it seems you just need some conditional aggregation. As you might not have a On/Off for each day, then you need to get the MIN/MAX for both event types, and then you can use ISNULL (or COALESCE):

WITH CTE AS(
    SELECT Name,
           [Date],
           MIN(CASE Event WHEN 'Event' THEN [Time] END) AS MinLogOnTime,
           MAX(CASE Event WHEN 'Event' THEN [Time] END) AS MaxLogOnTime,
           MIN(CASE Event WHEN 'Event' THEN [Time] END) AS MinLogOffTime,
           MAX(CASE Event WHEN 'Event' THEN [Time] END) AS MaxLogOffTime
    FROM dbo.YourTable
    GROUP BY Name,
             [Date])
SELECT Name,
       Date,
       ISNULL(MinLogOnTime,MinLogOffTime) AS MinLogOnTime,
       ISNULL(MaxLogOffTime,MaxLogOnTime) AS MaxLogOffTime,
       DATEDIFF(HOUR,ISNULL(MaxLogOffTime,MaxLogOnTime),ISNULL(MaxLogOffTime,MaxLogOnTime)) AS TimeDifference
FROM CTE;