CODEWITHSUNDEEP
t-sqldatetimejoin
Blair Cliff
Blair Cliff

Reputation: 53

Join together events from a table of start dates and end dates

I have one table that records whether an employee has clocked in and clocked out, but tracks those as separate rows.

Input:

+------------+------------+---------------+
| EmployeeID | Event_Date | Event_Type    |
+------------+------------+---------------+
| 2450770    | 2020/01/02 | 'Clocked Out' | -- Doesn't have a clocked in time within desired time range
| 2195326    | 2020/01/06 | 'Clocked In'  |
| 2195326    | 2020/01/10 | 'Clocked Out' |
| 800455     | 2020/01/15 | 'Clocked In'  |
| 2450770    | 2020/01/15 | 'Clocked In'  | -- No clock out time yet
| 800455     | 2020/01/22 | 'Clocked Out' | 
| 2195326    | 2020/01/23 | 'Clocked In'  | 
| 2331340    | 2020/01/25 | 'Clocked In'  |
| 2195326    | 2020/01/27 | 'Clocked Out' |
| 2331340    | 2020/02/01 | 'Clocked Out' |
| 2515957    | 2020/02/05 | 'Clocked In'  |
|            |            |               | -- etc

Desired Output:

+------------+------------+-------------+
| EmployeeID | Clocked_In | Clocked_Out |
+------------+------------+-------------+
| 2195326    | 2020/01/06 | 2020/01/10  |
| 800455     | 2020/01/15 | 2020/01/22  |
| 2450770    | 2020/01/15 | NULL        |
| 2195326    | 2020/01/23 | 2020/01/27  |
| 2331340    | 2020/01/25 | 2020/02/01  |
+------------+------------+-------------+

This problem seems simple enough, but I can't quite figure it out: How do I join those Clocked In and Clocked Out events together, such that I can see anyone who clocked in on January, with their corresponding clocked out date (if it exists) within no date restriction? Complications include people without matching clocked-in and clocked-out dates, people clocking in and out within different months, clocking in/out multiple times in a month. As far as I can tell, there are no cases of people clocking in twice in a row (or out twice in a row).

Upvotes: 1

Views: 31

Answers (1)

JoYi
JoYi

Reputation: 132

Something like this gets the row numbers of your data, partitioning by EmployeeID and ordering by Event_Date. Once you have that, then join on the employee id plus the next row number.

Doing it this way will handle most, if not all of your complications, including when the clocked in date is greater than the clocked out date.

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (EmployeeID VARCHAR(256), Event_Date DATE, Event_Type varchar(256))

INSERT INTO #temp
VALUES 
( '2450770', '2020/01/02', 'Clocked Out'),
( '2195326', '2020/01/06', 'Clocked In'),
( '2195326', '2020/01/10', 'Clocked Out'),
( '800455', '2020/01/15', 'Clocked In'), 
( '2450770', '2020/01/15', 'Clocked In'), 
( '800455', '2020/01/22', 'Clocked Out'),
( '2195326', '2020/01/23', 'Clocked In'), 
( '2331340', '2020/01/25', 'Clocked In'), 
( '2195326', '2020/01/27', 'Clocked Out'),
( '2331340', '2020/02/01', 'Clocked Out'),
( '2515957', '2020/02/05', 'Clocked In')

DROP TABLE IF EXISTS #output
SELECT 
    EmployeeID,
    Event_Date,
    Event_Type,
    ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Event_Date) AS RN
INTO #output
FROM #temp

SELECT 
    I.EmployeeID, 
    I.Event_Date AS [Clocked In], 
    O.Event_Date AS [Clocked Out]
FROM #output I 
LEFT JOIN #output O ON O.EmployeeID + O.RN = I.EmployeeID + (I.RN + 1) AND O.Event_Type = 'Clocked Out'
WHERE I.Event_Type = 'Clocked In'

Upvotes: 1

Related Questions