Return dates on which a team member was called back in more than once

Question

I am working in Azure SQL DB (SQL Server) and having trouble with a query that has grown more complex since I began working on the problem. I am working with timekeeping data that unfortunately, is not the cleanest.

Problem Statement:
Return results showing team members who were called in to work (a specific call-in PaycodeID - "CB"), then sent home and transferred to an on-call status (this creates a new row with a different on-call PaycodeID - "OC"), and were later called back into work (a third row with the call-in PaycodeID - "CB"). In plain English, the requirement is to find instances were an on-call employee was called into work more than once during a shift.

The Dataset:

| RowID | EmployeeID | Shift Date | StartDT             | EndDT               | PaycodeID |
|-------|------------|------------|---------------------|---------------------|-----------|
| 1     | 123        | 2020-02-13 | 2020-02-13 17:30:00 | 2020-02-13 19:00:00 | CB        |
| 2     | 123        | 2020-02-13 | 2020-02-13 19:00:00 | 2020-02-13 23:00:00 | OC        |
| 3     | 123        | 2020-02-13 | 2020-02-13 23:00:00 | 2020-02-14 03:00:00 | CB        |
| 4     | 456        | 2020-01-01 | 2020-01-01 06:00:00 | 2020-01-01 09:30:00 | OC        |
| 5     | 456        | 2020-01-01 | 2020-01-01 09:30:00 | 2020-01-01 12:00:00 | CB        |
| 6     | 456        | 2020-01-01 | 2020-01-01 12:30:00 | 2020-01-01 16:45:00 | CB        |
| 7     | 456        | 2020-01-01 | 2020-01-01 16:45:00 | 2020-01-01 18:00:00 | OC        |

T-SQL Query I tried:

SELECT 

    ,[EmployeeID]
    ,[Shift Date]
    ,SUM(
        CASE WHEN [PaycodeID] = "OC"
        THEN 1
        ELSE 0
        END
        )
     AS [On-Call Count]
    ,SUM(
        CASE WHEN [PaycodeID] = "CB"
        THEN 1
        ELSE 0
        END
         ) 
        AS [Call Back Count]

FROM 
    #OnCallTable

GROUP BY [Employee ID], [Shift Date]
ORDER BY [Employee ID], [Shift Date]

Results of this query:

| EmployeeID | Shift Date | On-Call Count | Call Back Count |
|------------|------------|---------------|-----------------|
| 123        | 2020-02-13 | 1             | 2               |
| 456        | 2020-01-01 | 2             | 2               |

I was then planning on selecting EmployeeID and Shift Date where the [Call Back Count] > 1. However, this would return both records in the above result set whereas only the first row should be returned. If you look back at my original dataset, employee 456 was only called into work once and ended up with two "CB" rows because they clocked out for a break at 12:00:00. I am trying to design a query that will only return shifts in which an employee has an entry of "OC" where the timestamp is between two or more "CB" entries.

Any ideas on how to approach this problem would be greatly appreciated.

user_10676716 · Answer

If I followed you correctly, you can solve this with lead() and lag():

select employeeID, shiftDate
from (
    select 
        oc.*,
        lead(startDT)   over(partition by employeeID, shiftDate order by rowID) leadStartDT,
        lead(paycodeID) over(partition by employeeID, shiftDate order by rowID) leadPaycodeID,
        lag(endDT)      over(partition by employeeID, shiftDate order by rowID) lagEndDT,
        lag(paycodeID)  over(partition by employeeID, shiftDate order by rowID) lagPaycodeID
    from #onCallTable oc
) t
where 
    paycodeID = 'OC'
    and lagPaycodeID = 'CB' 
    and leadPaycodeID = 'CB'
    and lagEndDT = startDT
    and leadStartDT = endDT

This brings rows with paycode OC, surrounded with paycodes CB, and whose dates are contiguous with the surrounding records.

Return dates on which a team member was called back in more than once

1 Answers