日期:2014-05-17  浏览次数:20558 次

合并考勤记录 计算出每个人一个月的考勤
表:KaoQin
SQL code

U_ID        Zaoshang                Wanshang        

1001        2012-9-1 08:39:00.000        2012-9-1 18:36:00.000

1001        2012-9-2 08:20:00.000        2012-9-2 18:46:00.000

1001        2012-9-3 08:26:00.000        2012-9-3 17:46:00.000

1002        2012-9-1 08:22:00.000        2012-9-1 18:12:00.000

1002        2012-9-2 08:22:00.000        2012-9-2 18:23:00.000

1002        2012-9-3 08:15:00.000        2012-9-3 18:26:00.000
......


要得到 一个月 每天 的 上下班 时间
如下样式:
SQL code

U_ID        1                2                3            4    5    6...    30
1001    08:20-18:36    08:20-18:46    08:26-17:46

1002    08:22-18:12    08:22-18:23    08:15-18:26



搞了很久实在搞不定 谢谢解答了


------解决方案--------------------
脚本如下:
SQL code


CREATE TABLE #Temp(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [U_ID] [nvarchar](50) NULL,
    [zaoshang] datetime NULL,
    [wanshang]datetime NULL 
     
)

insert into #temp
select '1001','2012-9-1 08:39:00.000','2012-9-1 18:36:00.000' union all
select '1001','2012-9-2 08:20:00.000','2012-9-2 18:46:00.000' union all
select '1001','2012-9-3 08:26:00.000','2012-9-3 17:46:00.000' union all
select '1002','2012-9-1 08:22:00.000','2012-9-1 18:12:00.000' union all
select '1002','2012-9-2 08:22:00.000','2012-9-2 18:23:00.000' union all
select '1002','2012-9-3 08:15:00.000','2012-9-3 18:26:00.000'

;with abc as (
select U_ID,cast (convert(varchar(10),zaoshang,8)+'-'+convert(varchar(10),wanshang,8) as nvarchar(20)) shijian, day(zaoshang) as riqi from #temp
 )
select U_ID,[1],[2],[3],[4]
from abc
pivot(max(shijian) for [riqi] in ([1],[2],[3],[4])) as pvt
 
drop table #Temp

------解决方案--------------------
SQL code


IF (OBJECT_ID('TBL')) IS NOT NULL DROP TABLE TBL
GO
CREATE TABLE TBL
(
U_ID INT,
Zaoshang DATETIME,
Wanshang datetime
)


INSERT INTO TBL
SELECT 1001,'2012-9-1 08:39:00.000','2012-9-1 18:36:00.000' UNION
SELECT 1001,'2012-9-2 08:20:00.000','2012-9-2 18:46:00.000' UNION
SELECT 1001,'2012-9-3 08:26:00.000','2012-9-3 17:46:00.000' UNION
SELECT 1002,'2012-9-1 08:22:00.000','2012-9-1 18:12:00.000' UNION
SELECT 1002,'2012-9-2 08:22:00.000','2012-9-2 18:23:00.000' UNION
SELECT 1002,'2012-9-3 08:15:00.000','2012-9-3 18:26:00.000'

GO
WITH CTE AS(
select 
U_ID,
datepart(dd,Zaoshang) work_day,
convert(nvarchar(16),Zaoshang,20)+'&'+convert(nvarchar(16),Wanshang,20) work_Time FROM TBL
)
SELECT U_ID,[1],[2],[3] FROM CTE pivot(MAX(work_time) FOR work_day IN ([1],[2],[3])) AS T