locked
How to Query for Daily Time Record? RRS feed

  • Question

  • I have the following table:

    ATTENDANCE.LOG

    ------------------------

    ID int, ID PK

    EmployeeID int

    LogModeID tinyint (0=log in, 1 = log out)

    TimeLog datetime

     

     

    How do I produce the following result:

    [Per EmployeeID]

    Date     |     AM Time-in     | AM Time-out     |     PM Time-in     |     PM Time-out

     

    Thank you.


    a filipino newbie
    Wednesday, March 24, 2010 2:23 PM

Answers

  • Assuming only one time-in/time-out in AM/PM

    select EmployeeID,

    max(case when LogModeId = 0 and datepart(hour, TimeLog) < 12 then TimeLog end) as [AM Time-in],

    max(case when LogModeId = 1 and datepart(hour, TimeLog) < 12 then TimeLog end) as [AM Time-out],

    max(case when LogModeId = 0 and datepart(hour, TimeLog) >= 12 then TimeLog end) as [PM Time-in],

    max(case when LogModeId = 1 and datepart(hour, TimeLog) >= 12 then TimeLog end) as [PM Time-out]

    from Attendance group by EmployeeID


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 24, 2010 2:51 PM
  • We need to use a calendar table here and do a left join from the calendar table to our table.

    See http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    • Marked as answer by superkuton Wednesday, March 24, 2010 3:58 PM
    Wednesday, March 24, 2010 3:57 PM

All replies

  • Assuming only one time-in/time-out in AM/PM

    select EmployeeID,

    max(case when LogModeId = 0 and datepart(hour, TimeLog) < 12 then TimeLog end) as [AM Time-in],

    max(case when LogModeId = 1 and datepart(hour, TimeLog) < 12 then TimeLog end) as [AM Time-out],

    max(case when LogModeId = 0 and datepart(hour, TimeLog) >= 12 then TimeLog end) as [PM Time-in],

    max(case when LogModeId = 1 and datepart(hour, TimeLog) >= 12 then TimeLog end) as [PM Time-out]

    from Attendance group by EmployeeID


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 24, 2010 2:51 PM
  • Thank you Naom
    a filipino newbie
    Wednesday, March 24, 2010 2:59 PM
  • How do I create the first column (Date) contain the values 1 to 31,

    then the other four-columns will show the time-in and time-out at the corresponding date?

     

    Example:

    [Per EmployeeID],

    [For the Month of March, 2010]

     

    Date     |     AM Time-in     | AM Time-out     |     PM Time-in     |     PM Time-out

    1          |      7:30 AM        |   12:10 PM        |     12:45 PM        |     5:20 PM

    2          |      7:30 AM        |   12:10 PM        |     12:45 PM        |     5:20 PM

    3          |      7:30 AM        |   12:10 PM        |     12:45 PM        |     5:20 PM

    4          |      7:30 AM        |   12:10 PM        |     12:45 PM        |     5:20 PM

    5          |      7:30 AM        |   12:10 PM        |     12:45 PM        |     5:20 PM

    6

    7

    8          |      7:30 AM        |   12:10 PM        |     12:45 PM        |     5:20 PM

    to

    31         |      7:30 AM        |   12:10 PM        |     12:45 PM        |     5:20 PM

     

    March 6 & 7 does not contain timelog because its saturday and sunday.

     

    thank you.


    a filipino newbie
    Wednesday, March 24, 2010 3:37 PM
  • Very similar to my original query, but we need to also add TimeLog and group by EmployeeID, TimeLog. Check CONVERT function to show the time only portion of the datetime - I always forget the number - 109?

    http://wiki.lessthandot.com/index.php/Data_formatting_dates

     

    Looks like there is no direct format available to show HH:MM am/pm.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Edited by Naomi N Wednesday, March 24, 2010 3:56 PM
    Wednesday, March 24, 2010 3:43 PM
  • How bout showing the dates 1 to 31 even when there's no timelog on those dates?

     

    thanks again.


    a filipino newbie
    Wednesday, March 24, 2010 3:53 PM
  • We need to use a calendar table here and do a left join from the calendar table to our table.

    See http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    • Marked as answer by superkuton Wednesday, March 24, 2010 3:58 PM
    Wednesday, March 24, 2010 3:57 PM
  • Take a look at Formatting the time from a datetime or time datatype by using the STUFF function

    (You're (and me) are lucky that Denis wrote a blog so quickly). 


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 25, 2010 2:53 AM