在SQL查询中需要帮助 [英] Need help in sql queries

问题描述

我正在做一个涉及时间和出勤管理的项目.当我从生物识别阅读器下载数据时，我得到了以下格式的记录，

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

我想显示以下记录，
(intime，break_out，break_in和outtime基于时间")

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

推荐答案

阅读我有关动态旋转的文章: ^ ]

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

尝试一下

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

我正在使用ncTimeClock，它在ASP.NET和Window窗体中都很好用. 我是从Netstair Communications 407-738-4504 www.netstair.com获得的，不到200.00

Al

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

这篇关于在SQL查询中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！