Click here to Skip to main content
12,063,909 members (65,247 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-server-2005 C# ASP.NET , +
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?..
Posted 20-Sep-11 0:43am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this
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
  Permalink  
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+
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160204.4 | Last Updated 20 Sep 2011
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100