1

I have a table that stores clock entries in the following order.

UID         Clock                    Status
===         =====                    ======

R01         2013-01-01 17:00:00      Clockin
R01         2013-01-01 17:10:00      Clockin
R01         2013-01-01 23:45:00      Clockin

My current solution is the use of Min/Max date with case and left join to arrange the entries in the following order

UID        Date         ClockIn    ClockOut
===        ====         =======    ========
R01        2013-01-01    17:00:00  23:45:00

How should I handle the the situation when the clock entry span past midnight. ie,

UID         Clock                    Status
===         =====                    ======
R01         2013-01-01 17:00:00      Clockin
R01         2013-01-02 00:45:00      Clockin

As the above will produce 2 entries like below which will result in zero time instate of 7.45 hours for the employee. 

UID        Date          ClockIn               ClockOut
===        ====          =======               ========
R01        2013-01-01    2013-01-01 17:00:00   2013-01-01 17:00:00
R01        2013-01-02    2013-01-02 00:45:00   2013-01-02 00:45:00

Thanks in advance.

Here's the code to the stored procedure use in SQL 2008R2

SELECT A.Device,A.DID, A.Name, A.ClockDate,Clockin ,ClockOut
FROM 
(
    SELECT Device,DID,Name, CONVERT(DATE, DeviceClock) 'ClockDate', 
    min(case when clock=Clock and Status ='Clock In' OR status='Clock Out' OR status='Access In' then clock  end) 'CLock In'

    FROM TABLE  
    group by Device,DID,Name, CONVERT(DATE, DeviceClock)
) as A
LEFT JOIN 
(
    SELECT Device,DID,Name, CONVERT(DATE, DeviceClock) 'ClockDate', 

max(case when clock=Clock and Status ='Clock in' or status='Clock Out' OR status='Access In' then Clock end) 'Clock Out'


    FROM TABLE 
    group by Device,DID,Name, CONVERT(DATE, DeviceClock)
) as B
ON A.DID = B.DID AND A.ClockDate = B.ClockDate
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Bladefreak
  • 331
  • 1
  • 6
  • 17
  • Please show an example of the query you are using now so we can suggest an alternative. Also, please identify the database software used. – BellevueBob Dec 31 '12 at 17:30
  • 1
    Seems like it would be much easier to get rid of Date and just have ClockIn and ClockOut be full DateTime fields. – Jordan Kaye Dec 31 '12 at 17:30
  • If you want to track dates as well, then you may need to use a `composite key (UID+DATE)`. OTherwise what Jordan says fits well. Clarify please. – bonCodigo Dec 31 '12 at 17:32
  • Yes. The clockin and clockout is stored in datetime type field. – Bladefreak Dec 31 '12 at 17:49
  • @user1833676 hi, im in the same scenario with a Time and Attendance project I have. Did you find a solution? Thanks a lot – VAAA Apr 28 '14 at 01:18
  • Hi I may ask about same problem about getting the date in different dates with type – Antolin Bernas Aug 17 '20 at 01:46

2 Answers2

2

There's a LOT you are not considering here. Time/Attendance systems are quite complex. For example:

  • What if your ClockIn is only a few minutes before midnight and the ClockOut is much later in the morning? Which day do you record the time to?
  • What if the time is split evenly, say 10PM to 2AM - is that one 4-hour shift on day 1? or day 2? Or is that two 2-hour shifts?
  • Are you recording time in UTC? Doing local conversions? Handling Daylight Savings Time? (out minus in at local time is NOT an accurate measurement of elapsed duration!)
  • Rounding? By how much? In what scenarios?
  • How to handle missing punches? Duplicate punches?

There are entire systems that have evolved to handle these scenarios. They also usually handle work rules like overtime calculation, job costing, scheduling, and other things - but they ALL start with the basic problem case you describe.

You don't always pair with the next punch, or the next of a particular type, or the next on the same date. There are multitudes of combinations of scenarios to deal with, and you can't express this in a simple sql statement or stored procedure. You can try - but you will have lots of edge cases to deal with.

I would consider purchasing a product that already handles these cases for you. If you are intent on writing your own - don't do it in SQL. Get the raw data back from SQL and use it with your business logic to produce a processing engine.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Hi Matt Johnson. Thanks for the input. Those that you have mention have been noted. The thing about this situation is that. These employee don't work on any schedule, as they can come and go without any fix time or schedule. some can come in to work twice in a day, where split shift have been created, and time rounded accordingly, with manual punch input to cover any miss punches. It is those few employee that works past midnight during closing that we need to take care of. as the current solution is to adjust their time using manual punch input.. – Bladefreak Dec 31 '12 at 18:21
  • Different T/A systems handle this scenario in different ways. Some cut off at midnight and create a new shift for the next day. Some allow you to re-open the closed period to make the modification, and some make the modification to the closed period as an adjustment on the next period. How you define a "closing" has a lot to do with it . – Matt Johnson-Pint Dec 31 '12 at 18:27
  • The closing I am referring to is closing of the shop door.. As mention, there are no defined shift. Employee comes in at 10PM and work till 00:45. they are paid 2.45 hours. – Bladefreak Dec 31 '12 at 18:51
1

What Matt said is a good general answer and read all the caveats he mentioned, but if your case is pretty simple, in that all employees start after 6am, leave before midnight and finish before let's say, 3am, you could fudge it pretty well like this:

SELECT Device,DID,Name, convert( DATE, dateadd(hour, -3, CONVERT(DATE, DeviceClock))) 'ClockDate', 

max(case when clock=Clock and Status ='Clock in' or status='Clock Out' OR status='Access In' then Clock end) 'Clock Out'

FROM TABLE 
group by Device,DID,Name, convert( DATE, dateadd(hour, -3, CONVERT(DATE, DeviceClock)))

in each of the two joined selects. You're effectively shifting the idea of when the day starts and ends by 3 hours. Of course if anyone works beyond 3am, or starts before 3am, this won't work.

Jonny Cundall
  • 2,552
  • 1
  • 21
  • 33
  • Yes - this is another approach - customizing the workday cutoff. It works when you have people that just stay late (ex. movie theaters), but it has problems for 24/7 operations (ex. hospitals). You might want a variable workday line that is different per employee or per group of employees. You still have to know how to handle if they work over this line. – Matt Johnson-Pint Dec 31 '12 at 18:31
  • Thanks Matt. Your input have been valuable. In our situation, the people stay late to clean up and won't stay beyond 1am. and thanks Jonny for the suggestion to a solution. – Bladefreak Dec 31 '12 at 18:57