SQL guru's - need help

Discussion in 'OT Technology' started by tragicher0, Jul 4, 2008.

  1. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    OK, im stumped, been doing databases for yrs now, but never had to do reporting like this crap.

    I have to do a shitload of DOT reporting for my company, one of the requirements is to determine drivers time between shifts.

    The application is ancient and doesnt count this anywhere, so to the database I go.

    The table has timestamps of when the drivers punched in and out, for lunch, etc, in a date\time datatype (yyyy-mm-dd 00:00:00) How can I sum the times into hours?

    For example, if someone punched in at 6am, went to lunch at 12, back at 1, out at 5, and got called back in at 7 and worked until midnight. I would need to sum the times between each event.

    Thanks in advance
     
  2. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    Which DBMS?
     
  3. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    is there a Time_In and Time_Out field for each record? If so, this function should help

    hour(Time_Out - Time_In)

    edit: assuming SQL Server
     
  4. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    SQL2k
     
  5. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    yes, there is a time in and time out.
     
  6. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
  7. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    thanks, could you give me an example of how I could use that? Havent used it in years and cant find any of my old scripts that I used it in.
     
  8. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Wait, so there is one line and it has both time_in and time_out? You want something like:

    SELECT SUM(DATEDIFF(minute, time_out, time_in))) / 60 GROUP BY person;

    ?
     
    Last edited: Jul 4, 2008
  9. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    Why not just
    SELECT SUM(DATEDIFF(hh, time_in, time_out)) GROUP BY person;
     
  10. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Because it chunks whole hours even if you go one minute over. He might even want to do it with seconds, so people get partial minute credit.
     
  11. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    ahhh, gotcha, rouding loss
     
  12. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    awesome, thats what I thought it might be.

    Thanks, ill give this a shot
     
  13. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    well shit, I just started working on this, and I was wrong, there is a seperate row for each event.

    like so
    EMPID ACTUALDATE EVENTTYPEID - blah blah
    0897553 2004-12-20 09:03:00.000 IN
    0897553 2004-12-20 21:34:00.000 OUT
     
  14. Karnejj

    Karnejj “A true conservative is one who can't see any diff OT Supporter

    Joined:
    Jan 9, 2008
    Messages:
    35,585
    Likes Received:
    0
    Location:
    UPGRADED USA Shutdown Today:8 derps
    Errrm .... assuming they all alternate IN .. OUT ... IN .... OUT, it's still fairly easy to recreate a table that has the structure suggested above, where you can use the DATEDIFF function.

    Create some work-in-progress tables. The first one you need sorted by empid and then by time, also give each row an integer ID that increments by 1. Might even just want to do one employee at a time.

    You should get something like:
    ROWID EMPID ACTUALDATE EVENTTYPEID
    1 0897553 2004-12-20 09:03:00.000 IN
    2 0897553 2004-12-20 21:34:00.000 OUT

    You can double-check whether they alternate IN and OUT by SELECTing any row with an even-numbered rowid that has an eventtypeid of "IN"
    If there are any, then it'll make things tougher.
    SELECT * FROM clocktable WHERE (rowid % 2 = 1) AND eventtypeid LIKE "IN" LIMIT 1;
    ...something like that.



    If they always alternate then, you can create a big table that can hold two rows of time data from the original table on a single row.
    ROWID EMPID ACTUALDATE1 EVENTTYPEID1 ACTUALDATE2 EVENTTYPEID2

    INSERT the contents of first temp table into the big table filling the fields (ROWID, EMPID, ACTUALDATE1, EVENTTYPEID1).

    UPDATE the big table fields (ACTUALDATE2 EVENTTYPEID2) with the time data from the temp table, but only on rows where the big table's ROWID is equal to the temp table ROWID MINUS ONE.

    I guess it'd look like
    UPDATE temptable LEFT JOIN bigtable ON temptable.rowID = bigtable.rowid -1 SET bigtable.ACTUALDATE2 = temptable.ACTUALDATE, bigtable.EVENTTYPEID2 = temptable.EVENTTYPEID


    Assuming everything alternated neatly, the big table should now have every EVEN row as the time worked, and ODD rows as the time that an employee was off (or vice versa). So you can use DATEDIFF on rows that have ACTUALDATE1 equal to OUT, if you want to calculate time off of work.


    There are probably better ways, but .... if nothing else is suggested, then you could give this one a go.
     
    Last edited: Jul 6, 2008
  15. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    thanks for the tips, not sure if thats the way I want to approach it, but I might have to, the problem is that there are lots of event types, and most dirvers might work more than one shift in a day (also, there are 10K drivers and the tcevent table that stores the punches is 3million rows for 6 months)
     
  16. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    First step would be to make a temporary table with an Autonumber as its primary key (TimeID) with the identical data you already have (Table1)

    Then create a 2nd table that has a primary key of PunchID, EmpID, PunchIn, PunchOut, Duration

    Append all of the 1st table's punch in data to the 2nd table:

    INSERT INTO Table2 ( EmpID, TimeIn, TimeID )
    SELECT Table1.EmpID, Table1.PunchTime, Table1.TimeID
    FROM Table1
    WHERE (((Table1.PunchInOut)=True));

    Create the matching punch out data in another temp table (something like this):

    INSERT INTO Table3 ( PunchInID, PunchOutID )
    SELECT Table2.TimeID, Min(Table1.TimeID)
    FROM Table1 INNER JOIN Table2 ON (Table1.EmpID=Table2.EmpID) AND (Table2.TimeID<Table1.TimeID)
    GROUP BY Table2.TimeID;

    Then update the PunchOut and Duration in 2nd temp by linking the 2nd table, with the 3rd table, to the 2nd table.

    UPDATE (Table2 INNER JOIN Table3 ON Table2.TimeID = Table3.PunchInId) INNER JOIN Table1 ON Table3.PunchOutID = Table1.TimeID SET Table2.TimeOut = [punchtime], Table2.Duration = [punchtime]-[timein];

    I left the duration in date/time format. I also didn't make the tables on the fly but you get the idea
     
    Last edited: Jul 6, 2008
  17. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    welp, im totally fucking stumped
     
  18. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    ok, I made 2 more tables, one has all the 'IN' punches and one has all the 'OUT punches (with all of the other crap that was in the tables)
     
  19. tragicher0

    tragicher0 Before I couldn't even spell DBA, now I are one OT Supporter

    Joined:
    Nov 19, 2005
    Messages:
    16,027
    Likes Received:
    7
    Location:
    Anthem, AZ
    and my fucking head hurts
     
  20. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You don't want two tables ultimately. What you want is one table with both the punch in and punch out data.

    I took the raw data you had and simply added an Primary Key first

    Next, I put all of the punch in data into the table with columns for both Punch In and Punch Out data, as well as a duration column. I retained the original TimeID - this is important.

    The only tricky part is, is to identify which is the matching Punch Out data. For that, you need to join those two tables on both the EmpID field, and the TimeID field - but the TimeID for the punch out must be greater than the punch in TimeID. Then to ensure I'm only finding one match, I use the Min command to find the lowest TimeID. Ultimately I'm storing a PunchInTimeID and a PunchOutTimeID into another table.

    Then it's a simple matter of updating the punchout time and the resulting duration of those two times. You can then do a number of functions to sum up how much time each person put in.
     
  21. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I'm reading this a little more and it looks like you actually want the time between the punch outs and the punch ins (which is opposite to what one would normally want). The same principle applies though - you actually put the punch out data first, and then find the next matching punch in data.
     
  22. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    This isn't hard. You want a self-join. If noone tells you how I'll post it in the next day or two.
     
  23. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    I hate using temporary tables where subqueries will work :o
    And I'd suggest adding a rowkey of sorts (autonumber), too.

    Something like...
    Code:
    SELECT ins.EMPID,SUM(DATEDIFF(minute,ins.ACTUALDATE,outs.ACTUALDATE))/60 AS Hours
    FROM timetable AS ins
    LEFT JOIN (SELECT ot.EMPID,MIN(ot.rowkey) AS rowkey FROM timetable AS ot WHERE ot.EVENTTYPEID='OUT' AND ot.rowkey>ins.rowkey AND ot.EMPID=ins.EMPID GROUP BY ot.EMPID) AS nextout ON nextout.EMPID=ins.EMPID
    LEFT JOIN timetable AS outs ON outs.rowkey=nextout.rowkey
    WHERE ins.EVENTTYPEID='IN' AND nextout.rowkey IS NOT NULL
    GROUP BY ins.EMPID
    
     

Share This Page