34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #Tmp_table
(
姓名','','VARCHAR(20),
工号','','VARCHAR(20),
部门','','VARCHAR(20),
上班时间','DATETIME,
下班时间','DATETIME,
工作时长','VARCHAR(20)
)
insert into #Tmp_table(姓名,工号,部门,上班时间,下班时间,工作时长)
select '张三','80101002','后勤','2012-09-29 15:36','2012-09-29 22:47','7小时11分' union all
select '张三','80101002','后勤','2012-09-27 15:54','2012-09-27 22:55','7小时1分' union all
select '张三','80101002','后勤','2012-09-26 15:30','2012-09-26 22:59','7小时29分' union all
select '张三','80101002','后勤','2012-09-25 15:25','2012-09-25 22:52','7小时27分' union all
select '张三','80101002','后勤','2012-09-24 15:26','2012-09-24 23:07','7小时41分' union all
select '张三','80101002','后勤','2012-09-23 15:29','2012-09-23 23:57','8小时28分' union all
select '张三','80101002','后勤','2012-09-22 15:22','2012-09-22 22:32','7小时10分' union all
select '张三','80101002','后勤','2012-09-21 15:13','2012-09-21 22:44','7小时31分' union all
select '张三','80101002','后勤','2012-09-20 15:12','2012-09-20 22:52','7小时40分' union all
select '张三','80101002','后勤','2012-09-19 15:08','2012-09-19 22:41','7小时33分' union all
select '张三','80101002','后勤','2012-09-18 15:27','2012-09-18 22:44','7小时17分' union all
select '张三','80101002','后勤','2012-09-17 15:16','2012-09-17 22:28','7小时12分' union all
select '张三','80101002','后勤','2012-09-16 15:13','2012-09-16 22:41','7小时28分' union all
select '张三','80101002','后勤','2012-09-15 15:17','2012-09-15 20:11','4小时54分' union all
select '张三','80101002','后勤','2012-09-14 15:25','2012-09-14 22:46','7小时21分' union all
select '张三','80101002','后勤','2012-09-13 15:27','2012-09-13 22:37','7小时10分' union all
select '张三','80101002','后勤','2012-09-12 06:53','2012-09-12 14:46','7小时53分' union all
select '张三','80101002','后勤','2012-09-11 15:29','2012-09-11 23:02','7小时33分' union all
select '张三','80101002','后勤','2012-09-11 12:26','2012-09-11 14:19','1小时53分' union all
select '张三','80101002','后勤','2012-09-10 15:25','2012-09-10 22:50','7小时25分' union all
select '张三','80101002','后勤','2012-09-09 15:25','2012-09-09 22:46','7小时21分' union all
select '张三','80101002','后勤','2012-09-08 15:06','2012-09-08 22:53','7小时47分' union all
select '张三','80101002','后勤','2012-09-07 15:48','2012-09-07 23:12','7小时24分' union all
select '张三','80101002','后勤','2012-09-06 15:22','2012-09-06 23:23','8小时1分'
说实话.分有点少哈~
SELECT A.姓名,A.工号,A.部门,B.上班时间,C.下班时间,
(CASE WHEN A.工作时长%60 = 0 THEN CONVERT(varchar(2),A.工作时长/60)+'小时0分'
ELSE CONVERT(VARCHAR(2),(A.工作时长-(A.工作时长%60))/60)+'小时'+CONVERT(varchar(2),A.工作时长%60)+'分' END)AS 工作时长
FROM (SELECT 姓名,工号,部门,CONVERT(VARCHAR(10),上班时间,121)AS 日期,SUM(DATEDIFF(mi,上班时间,下班时间))AS 工作时长
from #Tmp_table
group by 姓名,工号,部门,CONVERT(VARCHAR(10),上班时间,121)) A
LEFT OUTER JOIN (SELECT 工号,Min(上班时间)AS 上班时间,CONVERT(VARCHAR(10),上班时间,121)AS 日期
FROM #Tmp_table
GROUP BY 工号,CONVERT(VARCHAR(10),上班时间,121)) B
ON A.工号 = B.工号 AND A.日期 = B.日期
LEFT OUTER JOIN (SELECT 工号,MAX(下班时间)AS 下班时间,CONVERT(VARCHAR(10),上班时间,121)AS 日期
FROM #Tmp_table
GROUP BY 工号,CONVERT(VARCHAR(10),上班时间,121)) C
ON A.工号 = C.工号 AND A.日期 = C.日期
ORDER BY A.日期 DESC
思路是取张三某天的最早上班打卡时间和最晚下班时间.实际工作时长取某天的总工作时长
姓名 工号 部门 上班时间 下班时间 工作时长
-------------------- -------------------- -------------------- ----------------------- ----------------------- ----------
张三 80101002 后勤 2012-09-29 15:36:00.000 2012-09-29 22:47:00.000 7小时11分
张三 80101002 后勤 2012-09-27 15:54:00.000 2012-09-27 22:55:00.000 7小时1分
张三 80101002 后勤 2012-09-26 15:30:00.000 2012-09-26 22:59:00.000 7小时29分
张三 80101002 后勤 2012-09-25 15:25:00.000 2012-09-25 22:52:00.000 7小时27分
张三 80101002 后勤 2012-09-24 15:26:00.000 2012-09-24 23:07:00.000 7小时41分
张三 80101002 后勤 2012-09-23 15:29:00.000 2012-09-23 23:57:00.000 8小时28分
张三 80101002 后勤 2012-09-22 15:22:00.000 2012-09-22 22:32:00.000 7小时10分
张三 80101002 后勤 2012-09-21 15:13:00.000 2012-09-21 22:44:00.000 7小时31分
张三 80101002 后勤 2012-09-20 15:12:00.000 2012-09-20 22:52:00.000 7小时40分
张三 80101002 后勤 2012-09-19 15:08:00.000 2012-09-19 22:41:00.000 7小时33分
张三 80101002 后勤 2012-09-18 15:27:00.000 2012-09-18 22:44:00.000 7小时17分
张三 80101002 后勤 2012-09-17 15:16:00.000 2012-09-17 22:28:00.000 7小时12分
张三 80101002 后勤 2012-09-16 15:13:00.000 2012-09-16 22:41:00.000 7小时28分
张三 80101002 后勤 2012-09-15 15:17:00.000 2012-09-15 20:11:00.000 4小时54分
张三 80101002 后勤 2012-09-14 15:25:00.000 2012-09-14 22:46:00.000 7小时21分
张三 80101002 后勤 2012-09-13 15:27:00.000 2012-09-13 22:37:00.000 7小时10分
张三 80101002 后勤 2012-09-12 06:53:00.000 2012-09-12 14:46:00.000 7小时53分
张三 80101002 后勤 2012-09-11 12:26:00.000 2012-09-11 23:02:00.000 9小时26分
张三 80101002 后勤 2012-09-10 15:25:00.000 2012-09-10 22:50:00.000 7小时25分
张三 80101002 后勤 2012-09-09 15:25:00.000 2012-09-09 22:46:00.000 7小时21分
张三 80101002 后勤 2012-09-08 15:06:00.000 2012-09-08 22:53:00.000 7小时47分
张三 80101002 后勤 2012-09-07 15:48:00.000 2012-09-07 23:12:00.000 7小时24分
张三 80101002 后勤 2012-09-06 15:22:00.000 2012-09-06 23:23:00.000 8小时1分
(23 行受影响)
select '0001' xh,1 [state],'2013-06-29 06:10:00' [time]
into #t
union all select '0001',2,'2013-06-29 08:13:20'
union all select '0001',1,'2013-06-29 09:00:03'
union all select '0001',2,'2013-06-29 18:00:03'
select xh,[state],[time]
from
(
select *,row_number() over(Partition by [state], convert(date,[time]) order by [time]) rn
from #t
) t
where rn=1
IF(OBJECT_ID('TA') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA(xh VARCHAR(10),[state] INT ,[time] DATETIME)
INSERT INTO TA(xh,[state],[time])
SELECT '0001',1,'2013-06-29 06:10:00' UNION ALL
SELECT '0001',2,'2013-06-29 08:13:20' UNION ALL
SELECT '0001',1,'2013-06-29 09:00:03' UNION ALL
SELECT '0001',1,'2013-06-29 18:00:03'
WITH CTE_A AS (
SELECT xh,[state],MIN([time]) AS [time] FROM TA
GROUP BY xh,[state]
)
DELETE FROM TA
WHERE NOT EXISTS (SELECT * FROM CTE_A AS L
WHERE TA.xh=L.xh AND TA.[state]=L.[state]
AND TA.[time]=L.[time]
)
--结果集
xh state time
---------- ----------- -----------------------
0001 1 2013-06-29 06:10:00.000
0001 2 2013-06-29 08:13:20.000