Take the 2-minute tour ×

I have a table in sql 2008 named tblEmployeeAttendance like enter image description here

Here is some dummy data for a employee's one days attendance like

enter image description here

Here AI refers AttendanceIn,AO refers AttendanceOut,BO refers BreakIn and BI refers BreakOut.There can multiple row in a day for a employee.First row should be AI,last row should be AO and others rows will be BreakIn,BreakOut Sequentially(that means BreakIn-BreakOut-BreakIn.....).

My expected output would be like this

enter image description here

I need a sql query that will return one row per date and return data that must contain AttendanceIn and AttendanceOut Time depands on strRemark(AI & AO). Please help me.

share|improve this question
What have you tried? Also, I would not reference the column names with "int", "dte" and "str". –  Darren Davies Jun 27 '12 at 11:50
add comment

1 Answer

up vote 2 down vote accepted
 dteAttendanceDate as 'Attendance Date'
 min(dteAttendanceTime) as 'Attendance In'
 max(dteAttendanceTime) as 'Attendance Out'

You probably dont need the AI and AO checks as the time will always be at opposite ends of the day.

share|improve this answer
wow it's so simple.thank you so much @benni_mac_b –  yeasir007 Jun 27 '12 at 12:03
add comment

Your Answer


