Search code examples
sql-serverjoininner-joindatabase-cursor

Need to match ClockIn with Clock out, multiple clocks on same day SQL

I'm trying to get the clock in and clock out times to correspond to each other so that I can calculate hours worked/amount of breaks/time worked within working hours etc. I have a 'Clocking' table that looks like this:

MOCK DATA:

Clock_ID Employee_ID Office Clock_Date Clock_Time ActivityID
1 83 Pretoria CBD 29/03/2022 06:43:00 1
2 55 Pretoria CBD 29/03/2022 06:45:00 1
3 54 Pretoria CBD 29/03/2022 07:00:00 1
4 80 Pretoria CBD 29/03/2022 07:00:00 1
5 75 Pretoria CBD 29/03/2022 07:05:00 1
6 54 Pretoria CBD 29/03/2022 10:59:00 2
7 54 Pretoria CBD 29/03/2022 11:50:00 1
8 55 Pretoria CBD 29/03/2022 12:18:00 2
9 55 Pretoria CBD 29/03/2022 12:30:00 1
10 83 Pretoria CBD 29/03/2022 13:03:00 2
11 80 Pretoria CBD 29/03/2022 13:04:00 2
12 83 Pretoria CBD 29/03/2022 13:39:00 1
13 80 Pretoria CBD 29/03/2022 13:39:00 1
14 75 Pretoria CBD 29/03/2022 15:59:00 2
15 54 Pretoria CBD 29/03/2022 16:00:00 2
16 83 Pretoria CBD 29/03/2022 16:00:00 2
17 80 Pretoria CBD 29/03/2022 16:00:00 2
18 55 Pretoria CBD 29/03/2022 16:00:00 2
19 83 Pretoria CBD 30/03/2022 06:46:00 1
20 55 Pretoria CBD 30/03/2022 06:51:00 1
21 80 Pretoria CBD 30/03/2022 06:54:00 1
22 54 Pretoria CBD 30/03/2022 06:54:00 1
23 54 Pretoria CBD 30/03/2022 11:24:00 2
24 54 Pretoria CBD 30/03/2022 12:11:00 1
25 80 Pretoria CBD 30/03/2022 13:03:00 2
26 80 Pretoria CBD 30/03/2022 14:10:00 1
27 54 Pretoria CBD 30/03/2022 16:01:00 2
28 80 Pretoria CBD 30/03/2022 16:01:00 2
29 83 Pretoria CBD 30/03/2022 16:01:00 2
30 55 Pretoria CBD 30/03/2022 16:05:00 2
31 83 Pretoria CBD 31/03/2022 06:48:00 1
32 55 Pretoria CBD 31/03/2022 06:53:00 1
33 54 Pretoria CBD 31/03/2022 06:55:00 1
34 80 Pretoria CBD 31/03/2022 07:09:00 1
35 54 Pretoria CBD 31/03/2022 12:02:00 2
36 54 Pretoria CBD 31/03/2022 12:09:00 1
37 83 Pretoria CBD 31/03/2022 12:34:00 2
38 80 Pretoria CBD 31/03/2022 12:34:00 2
39 83 Pretoria CBD 31/03/2022 13:09:00 1
40 80 Pretoria CBD 31/03/2022 13:09:00 1
41 55 Pretoria CBD 31/03/2022 13:32:00 2
42 54 Pretoria CBD 31/03/2022 15:56:00 2

The ActivityID determines whether it is a 'Clock IN' or 'Clock OUT'

I have used hash tables to separate the data into #clockIN and #clockOUT tables for any given Employee_ID as seen below:

#ClockIN

enter image description here

#ClockOUT

enter image description here

I tried using an inner join on the 'Clock_Date' on the tables to correspond clockINs to clockOUTs, but I get multiple clockOUTS for one clockIN due to the fact that employees can clockIN and OUT multiple times in a day: See below:

enter image description here

As you can see, the records are matched incorrectly, and a just joined on the date.

I cannot think of a way to correctly join them I.e ClockIn at 7:30 must be joined with the EARLIEST clock out on the same day and the next clockIN for that day must be the second earliest ClockIN which must be joined with the second earliest Clock OUT etc.

Would I have to use a cursor? if so, how could I implement that?

I will past all the SQL I used to get to this point.

Drop Table #ClockIn
Drop Table #ClockOut
DROP Table #SortedTimes
DROP Table #WorkBoundries

SELECT Clock_ID, Clock_Date AS Date, Clock_Time
    INTO #ClockIn 
    FROM Clocking
    WHERE Employee_ID = 82 AND ActivityID = 1
    SELECT * FROM #ClockIn

SELECT Clock_ID, Clock_Date AS Date, Clock_Time
    INTO #ClockOut 
    FROM Clocking
    WHERE Employee_ID = 82 AND ActivityID = 2
    SELECT * FROM #ClockOut
            
SELECT #ClockIn.Clock_Time As clockIN, #ClockOut.Clock_Time as ClockOUT, #ClockIn.Date INTO #SortedTimes
FROM #ClockIn
INNER JOIN #ClockOut On #ClockOut.Date = #ClockIN.Date
ORDER BY #ClockIn.Date ASC

SELECT * FROM #SortedTimes

SELECT MIN(clockIn)As TimeIn, MAX(ClockOUT) As TimeOut, [Date] As DayWorked INTO #WorkBoundries
FROM #SortedTimes
GROUP BY [Date]
SELECT * FROM #WorkBoundries

The #WorkBoundries is just to see if the person is clocking IN/OUT within their work hours or not.

Thank you in advance for any assistance

Solution

  • Maybe this can help you

    select e.empid,
       e.clockdate,
       e.clocktime as starttime,
       o.clocktime as endtime
from   emp e
  outer apply (select top 1 e2.clocktime 
               from   emp e2 
               where  e2.empid = e.empid
               and    e2.activity = 2
               and    e2.clockdate = e.clockdate
               and    e2.clocktime > e.clocktime
               order by e2.clocktime
              ) o
where  e.activity = 1
order by e.empid, e.clocktime

    DBFiddle here

    it results in this

    empid clockdate starttime endtime
    55 2022-03-29 06:45:00.0000000 12:18:00.0000000
    55 2022-03-29 12:30:00.0000000 null
    83 2022-03-29 06:43:00.0000000 13:03:00.0000000
    83 2022-03-29 13:39:00.0000000 16:00:00.0000000