Search code examples
sqlt-sqltimereporting

Matching up times in SQL

I have a database with agents call activity and I'm looking for instances where the agent has put themselves as busy for more than three minutes and then gone onto a break within a minute of that. My results need to show the Break row and the Busy row.

I've got this far but my head is round the bend with it - any help I'd appreciate it!!

SELECT * 
FROM
    DBO.AgentActivityReport Ar1

    Inner join dbo.AgentActivityReport Ar2
    on Ar1.[User_ID] = Ar2.[User_ID]
WHERE 
(
    Ar1.[ACTIVITY] = 'Busy'
    AND
    Ar1.[DURATION] > '00:03:00.0000000'
)
OR 
(
    Ar1.[ACTIVITY] LIKE 'Break (Tea Break)'
    AND
    Ar1.[DURATION] > '00:10:00.0000000'
)
OR
(
    Ar1.[ACTIVITY] LIKE 'Break (Lunch Break)'
AND
    Ar1.[DURATION] > '00:30:00.0000000'
)

AND Ar1.Activity in (select Ar2.Activity from dbo.AgentActivityReport Ar2 where Ar2.Activity in ('Break (Tea Break)','Break (Lunch Break)'))

ExampleData

Solution

  • Hmmm . . . I think this does what you want:

    SELECT * 
FROM DBO.AgentActivityReport Ar1 JOIN    
     dbo.AgentActivityReport Ar2
     ON Ar1.[User_ID] = Ar2.[User_ID]
WHERE (Ar1.[ACTIVITY] IN ('Busy')
       Ar1.[DURATION] > '00:03:00.0000000'
      ) AND
      (Ar1.[ACTIVITY] IN ('Break (Tea Break)', 'Break (Lunch Break)') AND
       DATEDIFF(SECOND, AR1.FINISH, AR2.START) < 60
      );

    Your question says nothing about the duration of the "break", only about the time in-between.