Click here to Skip to main content
15,881,516 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
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')

Myself little confused with my query. lol. How do i proceed?..

Share this answer
Try this
select empCode, [Date], [1] intime ,[2] break_out,[3] break_in,[4] outtime
(select empcode,convert(date,[DATE]) [date],convert(time,[time]) time ,ROW_NUMBER() over(partition by empcode,date order by empcode,date,time) Tin from emp
    for Tin in ([1],[2],[3],[4])
Share this answer
arun.emm 20-Sep-11 7:03am    
Hi ,

select emp_Code, [Dates], [1] intime ,[2] break_out,[3] break_in,[4] outtime
(select emp_code,convert(date,[DATES]) [date],convert(time,[times]) time ,ROW_NUMBER() over(partition by emp_code,dates order by emp_code,dates) Tin from punch_details
for Tin in ([1],[2],[3],[4])

the above query returns the following error ,
"Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel".
sachin10d 20-Sep-11 7:14am    
Change your database Compatibility level to higher version
right click on database>>properties>>Options>>Compatibility level change the compatibility
sachin10d 20-Sep-11 7:16am    
check this
Tejas Vaishnav 20-Sep-11 7:39am    
Nice one sachin10d

My 5+
I am using the ncTimeClock and it works great with ASP.NET and Window form.
I got it from Netstair Communications 407-738-4504 less than 200.00

Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900