Need help in sql queries

Question

4.50/5 (2 votes)

See more:

SQL-Server-2005

, +

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 19-Sep-11 23:43pm

arun.emm

Add a Solution

3 solutions

Solution 1

read my article about dymanic pivotting: Dynamic Pivoting with Cubes and eventhandlers in SQL Server 2005, 2008 and 2008 R2[^]

Posted 20-Sep-11 0:16am

Herman<T>.Instance

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

Posted 20-Sep-11 13:20pm

mashisp

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

sachin10d · Accepted Answer · 2011-09-20T00:53:00

Solution 2

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

Posted 20-Sep-11 0:53am

sachin10d

Updated 20-Sep-11 0:56am

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+