4 replies
by vamsikrish7 Accepted solution
by tony hopkinson Accepted solution
I'd start by relating ins and outs
So you end up with
EmployeeID, SwipedIn, SwipedOut
Then I'd have a shiftPattern table
e.g. ShiftID, StartTime, EndTime
Possibly if employees could be on different shift patterns and link table employid and shiftpattern.
At fhat point
Shift 1 Sun (06:30 - 14:30)
Should have 0 - n records that overlap it.
e.g.
Emp1 In 06:15 out 14:15
Emp2 In 06:45 out 14:45 etc
So if you join by shift number to InOut records, and then get you min and max by shift...
Might be a bit over the top, but you can work your way through it with temporary tables.
Tip if the data is required for fixed period e.g. last week. You can use shiftpattern to build a query that returns the datetimes of the shifts for last week, which should make joining InsAndOuts to shifts a lot easier.
by tobif Accepted solution
You may have situations, where someone works more than one shift within 24 hours.
So you need to stop thinking in terms of "dates".
You could identify shift by "Swipe in time".
Use Excel way to handle date&time (one floating point number, where the integer part is the date, and the decimals show the time. YYMMDD hh.mm etc are just presentation layer).
This allows to sort all ins&outs in ascending order and correlate start & end of shifts, as well as subtract in from out to get the shift duration.
by oldbaritone Accepted solution
There are lots of ways to handle this, but the essence is the same - if your work times are going to cross over from one day into the next, then the day must be part of the format, or it will be difficult to do calculations.
Excel's floating-point datevalue.timevalue format, where the integer portion represents the date, and the fractional portion represents the time (as a fraction of the day) is one way to do it.
Another is the military-style YYYYMMDDhhmmss format, which has the advantage of being easy to handle either as a number or a string, and sorts easily.
A third variation is the modified-Julian date format with time, YYYYJJJhhmmss. Julian day is simply the day of the year numbered consecutively, so 001 is January 1, 032 is February 1 (the day after January 31), 060 is March 1 in a regular (non-leap) year (31 days in January, plus 28 in February, is 59, so the next day, 60, is March 1) and so on. 001-365, or 366 in a leap year. Or if you want, you can go to standard Julian dates, used by NASA and other agencies who track things like orbital data.
Julian date can be very handy for planning and calculation, because it's very easy to add and subtract: What date is 90 days from 2010107? Answer: 2010197. Now, without looking I couldn't tell you what the calendar day was for either of those, but when you're attempting to calculate durations, Julian can be handy.
And once you change to a date-time format, Min() and Max() become very easy, regardless of which system you use.
Also remember that what your system uses "behind the scenes" does not need to be what the user sees routinely: in fact, it probably shouldn't be. Type a date into Excel, then override the cell format to a floating-point number. You'll have no idea what the number means. (Neither would I) - but so what? Excel handles it internally, knows what it is, and how to deal with it. Your system can be the same.
So the clock says "Oct 12 10:00" but internally it's "20101012100000" or "2010284100000"
Format the date to the user for display and printing, but deal with it internally as a complete date-time datum.
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending