sql time and attendance

[Arun.Emm]

Hi ,

i'm doing a project which invovles time and attendance management. When i download datas from the biometric reader , i got the records in the following format,

empCode date time
5001 12/09/2011 09:05:34
5002 12/09/2011 09:33:13
5001 12/09/2011 13:05:53
5002 12/09/2011 13:22:24
5001 12/09/2011 14:05:22
5002 12/09/2011 14:33:53
5001 12/09/2011 18:05:09
5002 12/09/2011 17:44:34

i want to show the above records as follows ,
(the intime , break_out , break_in and outtime are based on 'time')

empCode date intime break_out break_in outtime
5001 12/09/2011 09:05:34 13:05:53 14:05:22 18:05:09
5002 12/09/2011 09:33:13 13:22:24 14:33:53 17:44:34

so i tried the following query but it didnt work,

SELECT a.emp_Code, a.dates, a.times AS intime, b.break_out , c.break_in , d.outtime
FROM punch_details AS a LEFT OUTER JOIN
(((SELECT emp_code, dates, times AS break_out
FROM punch_details
WHERE (times > '13:00:00') and (times < '13:30:00')) AS b LEFT OUTER JOIN
(SELECT emp_code, dates, times AS break_in
FROM punch_details
WHERE (times > '13:30:00') and (times < '14:30:00')) AS c
on b.emp_code=c.emp_code and b.dates = a.dates) LEFT OUTER JOIN
(SELECT emp_code, dates, times AS outtime
FROM punch_details
WHERE (times > '17:00:00')) AS d on c.emp_code=d.emp_code and c.dates = d.dates) ON A.emp_code = b.emp_code AND A.dates = b.dates
WHERE (A.times > '09:00:00') and (A.times < '13:00:00')

How do i proceed?..

[lolafuertes]

I reproduced the problem using the following punch_details table definition in SQL 2008:
ColumnName Data Type
empCode int
date date
time time

Then I filled it with your example data and I wrote the following query:

SELECT a.empCode, a.[date], a.[time] AS intime, b.[time] as break_out, c.time as break_in, d.time as outtime
FROM punch_details AS A 
LEFT JOIN punch_details as B on a.empCode = b.empCode and a.date = b.date and  b.[time] > a.[time] 
LEFT JOIN punch_details as C on b.empCode = c.empCode and b.date = c.date and  c.[time] > b.[time] 
LEFT JOIN punch_details as D on c.empCode = d.empCode and c.date = d.date and  d.[time] > c.[time] 
WHERE NOT d.time is null

that produced the following results:

empCode date intime break_out break_in outtime
----------- ---------- ---------------- ---------------- ---------------- ----------------
5001 2011-09-12 09:05:34.0000000 13:05:53.0000000 14:05:22.0000000 18:05:09.0000000
5002 2011-09-12 09:33:13.0000000 13:22:24.0000000 14:33:53.0000000 17:44:34.0000000

(2 row(s) affected)

Hope this helps