SQL 高手请看！！！

Question

sql数据库考勤

Answer 1

是个综合设计问题，涉及班次（应该有这需求，对于多数工厂，小公司可能没有）、补卡、请假、日历表等
别想着用几个SQL完成结果

Answer 2

自由班次，必须规定一下基本规则：
1)上下班不能跨末个时点（SQL 中为 04:00）
2)最多打卡6次（次数可以扩充，总之得固定）
由于没有班次规则，忘记打卡只能靠人工检查后补记录。
WITH CheckInOut(UserID,CheckTime) AS (
    SELECT 1,Convert(datetime,'2014-10-06 09:03.43',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-06 12:01:07',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-06 13:57:12',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-06 13:58:00',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-06 18:05:34',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-06 19:29:59',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-06 23:15:28',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-07 08:50:51',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-07 12:04:41',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-07 13:56:39',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-07 18:01:55',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-07 19:35:16',120) UNION ALL
    SELECT 1,Convert(datetime,'2014-10-08 00:15:00',120) --跨日下班
)
,t1 AS ( -- 单独求出日期。按时间求序列号。
    SELECT UserID,
           Convert(datetime,
                   Convert(varchar(10),
                           DateAdd(hour,-4,CheckTime), -- 04:00 之前算前一天的打卡
                           120),
                   120) AS Date,
           CheckTime,
           ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY CheckTime) rn1
      FROM CheckInOut
)
,t2 AS ( -- 按日期分组求序列号。60秒内算连续打卡，只留前1条。
    SELECT UserID,
           Date,
           CheckTime,
           ROW_NUMBER() OVER(PARTITION BY UserID,Date ORDER BY CheckTime) rn2
      FROM t1
     WHERE NOT EXISTS (SELECT *
                         FROM t1 t
                        WHERE t.UserID = t1.UserID
                          AND t.rn1 = t1.rn1+1
                          AND DateDiff(second,t1.CheckTime,t.CheckTime)<=60
                      )
)
SELECT UserID,
       Date,
       [1] AS [上班1],
       [2] AS [下班1],
       [3] AS [上班2],
       [4] AS [下班2],
       [5] AS [上班3],
       [6] AS [下班3],
       ( DateDiff(second,[1],[2])
       + DateDiff(second,[3],[4])
       + DateDiff(second,[5],[6])
       ) / 3600.0 WorkHours
  FROM t2
 PIVOT (
        MAX(CheckTime)
        FOR rn2 IN ([1], [2], [3], [4], [5], [6])
       ) AS p
 UserID Date                    上班1                   下班1                   上班2                   下班2                   上班3                   下班3                                                 WorkHours
------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ---------------------------------------
      1 2014-10-06 00:00:00.000 2014-10-06 09:03:00.430 2014-10-06 12:01:07.000 2014-10-06 13:58:00.000 2014-10-06 18:05:34.000 2014-10-06 19:29:59.000 2014-10-06 23:15:28.000                               10.852777
      1 2014-10-07 00:00:00.000 2014-10-07 08:50:51.000 2014-10-07 12:04:41.000 2014-10-07 13:56:39.000 2014-10-07 18:01:55.000 2014-10-07 19:35:16.000 2014-10-08 00:15:00.000                               11.980555

Answer 3

坑爹啊！文本格式怎么出来 skype 链接了？
IE 的加载项已经禁用了，难道是 CSDN 脚本做的转换？

Answer 4

结果我还是把数据在取出来在C#里面用各种循环判断出来，但是这样效率没SQL高

Answer 5

#12
不同的班次要用不同的截止时间：
班次设置表(ID,截止时间)
排班表(UserID,日期,班次ID,开始时间,结束时间）后两个字段根据班次设置表计算出来，这样就可以按各自的时间段来划分统计日期了。