Click here to Skip to main content
15,072,051 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,

SQL
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?..
Posted

   
Try this
SQL
select empCode, [Date], [1] intime ,[2] break_out,[3] break_in,[4] outtime
from
(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
)data
pivot
(
    max([time])
    for Tin in ([1],[2],[3],[4])
)p
   
v2
Comments
arun.emm 20-Sep-11 7:03am
   
Hi ,

select emp_Code, [Dates], [1] intime ,[2] break_out,[3] break_in,[4] outtime
from
(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
)data
pivot
(
max([times])
for Tin in ([1],[2],[3],[4])
)p

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
http://msdn.microsoft.com/en-us/library/bb510680.aspx
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 www.netstair.com less than 200.00

Al
   

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