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:

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

#ClockOUT

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:

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

CodePudding user response：

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

CodePudding user response：

You can do this in a single scan of the base table, and no joins, by using window functions:

WITH NextValues AS (
    SELECT *,
      NextClockOut = LEAD(CASE WHEN c.ActivityID = 2 THEN c.Clock_Time END)
        OVER (PARTITION BY c.Office, c.Employee_ID, c.Clock_Date
             ORDER BY c.Clock_Time)
    FROM Clocking c
)
SELECT
  nv.Employee_ID,
  nv.Office,
  nv.Clock_Date,
  ClockIn = nv.Clock_Time,
  ClockOut = nv.NextClockOut
FROM NextValues nv
WHERE nv.ActivityID = 1;

db<>fiddle