0

Kindly share suitable technique to handle night shift as well as day shift. Below are the shifts,

Night,  2020-07-20 17:00 to 2020-07-21 02:00
Night,  2020-07-20 17:00 to 2020-07-21 02:00
Day,    2020-07-20 08:00 to 2020-07-20 17:00
Day,    2020-07-20 08:30 to 2020-07-20 17:30

In Database there are only two fields

1: SSN (which is employee Code)
2: CheckTime e.x. (2020-07-20 16:48:30.000)

in Day shift i can get first in last out from Min entry and Max entry. but in night shift I can't get any accurate data because of the date change.

Improve this question
4
  • When you say handle you mean to find out who is the first employee to start working and the last employee to stop working at the same day or to find out the time one employee starts and the time that same employee ends his shift?
    – Ronaldo
    Commented Jul 27, 2020 at 1:21
  • I need to manage time of one employee that starts and end shift in my application.
    – Zeeshan Malik
    Commented Oct 22, 2020 at 10:02
  • To achieve that you could use the function dateadd using the hour option like this: SELECT DATEADD(HOUR, 20, GETDATE()),GETDATE();. If you have any problems using it, tell me and I can try to help you writing the query.
    – Ronaldo
    Commented Oct 22, 2020 at 12:50
  • Generally speaking, there's no reliable algorithm that can fully infer shift lengths from a mere list of checkpoint times or even in/out times. Human inference has some success, but that's based on a dynamic knowledge of normal work patterns and unusual business events, plus an ability to identify and react sensibly to anomalies, plus the ability to seek further information in the hardest of cases.
    – Steve
    Commented Nov 1, 2023 at 9:29

1 Answer 1

Reset to default
0

Could you write a query to use BETWEEN the shift start date time and the shift end date time (both held in variables) that returns all the SSN and check times in that period? If you insert the results of that into a temporary table, you could then have another query on that temporary table for your MIN and MAX values. Doing it like that means you don't need to worry about a change of date because your only concerned with the data in your temporary table. Also, using variables would let you reuse the script for different date and times for shifts.

Improve this answer
1
  • Can you please write the query even a dummy data query. how would i manage enteries in temporary table to take min and max values in specific date when user's time in is before shift time in and user time out is after shift time out.
    – Zeeshan Malik
    Commented Oct 23, 2020 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.