Questions

How to get the min time and max time by using group by date

+
0 Votes
Locked

How to get the min time and max time by using group by date

vamsikrish7
We Have a swipe machine it will record all the data i.e intime and outimes of employees,I want to get the minimum intime and outime ,I have got it by using min and max functions and by using group by date,but we have Employees who start at evening and work for the next day also ,so my query is failing for these employees,I tried to solve this problem by subtracting 6 and half hours from the time recorded so that the recorded data falls in one date,then i used min and max functions and group by date and finally addedd 6 and half hours to the min and max times then i got all the shifts data also perfectly,but in case there is any shift starting at 6'o clock this query will fail,I want a better solution for this problem can u please help me.
  • +
    0 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    TobiF

    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.

    +
    0 Votes
    oldbaritone

    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.

  • +
    0 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    TobiF

    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.

    +
    0 Votes
    oldbaritone

    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.