# How to Query for Daily Time Record?

• ### Question

• I have the following table:

ATTENDANCE.LOG

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

ID int, ID PK

EmployeeID int

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

• 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.

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 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 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.

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 Wednesday, March 24, 2010 3:58 PM
Wednesday, March 24, 2010 3:57 PM
• (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