本帖最后由 hessen 于 2022-2-28 20:24 编辑
考勤数据整理
通过各类考勤设备员工打卡后，会将员工打卡信息全部都记录下来。到了月底会有海量的数据，这些数据中有些是有效的，有些是无效的，还有些是重复的。我们做考勤时需要取到其中有效的数据来计算出勤情况。
某单位根据管理要求打卡规则如下：
10月1日之前 7:10-7:40 9:50-10:20 14:20-14:50 16:20-16:50 18:50-19:20
10月1日之后 7:10-7:40 9:50-10:20 13:40-14:10 15:40-16:10 18:40-19:10
其中10月1日前后作为不同的判断依据，将全天的考勤时间分为5个时间段，在时间段内是有效的考勤记录，之外是无效考勤记录。有效时间段内如果有重复只记录最早打卡的一次时间。
下面是导出后录入的原始数据
写一条表间公式将不重复的有效的数据提取出来：
填报执行后得到结果如下数据：
实现思路：
方法不唯一。
1、整理数据，将日期和时间依据5个不同的时间段进行分组。 2、分组后将每个组内的数据按时间排升序后第一笔数据标记为1。 3、表间公式提取标记为1的数据。
参考数据整理脚本：
- WITH CC AS (
- SELECT [F_52314] AS 工号
- ,[F_52315] AS 日期
- ,[F_52316] AS 星期
- ,[F_52317] AS 时分
- ,[F_52318] AS 操作
- ,DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) AS 时间
- ,CASE
- WHEN MONTH([F_52315]) < 10 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 07:10' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 07:40' THEN 'A'
- WHEN MONTH([F_52315]) < 10 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 09:50' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 10:20' THEN 'B'
- WHEN MONTH([F_52315]) < 10 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 14:20' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 14:50' THEN 'C'
- WHEN MONTH([F_52315]) < 10 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 16:20' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 16:50' THEN 'D'
- WHEN MONTH([F_52315]) < 10 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 18:50' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 19:20' THEN 'E'
-
- WHEN MONTH([F_52315]) > 9 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 07:10' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 07:40' THEN 'F'
- WHEN MONTH([F_52315]) > 9 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 09:50' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 10:20' THEN 'G'
- WHEN MONTH([F_52315]) > 9 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 13:40' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 14:10' THEN 'H'
- WHEN MONTH([F_52315]) > 9 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 15:40' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 16:10' THEN 'I'
- WHEN MONTH([F_52315]) > 9 AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) >='1900-01-01 18:40' AND DATEADD(d,DATEDIFF(d,[F_52315],'1900-01-01') ,F_52315 + F_52317) <= '1900-01-01 19:10' THEN 'J'
- ELSE 'O'
- END AS 分组
- FROM [DemoSys].[dbo].[T_1272]
- WHERE 1 = 1
- )
- SELECT 工号,日期,星期,时分,操作,时间,分组,ROW_NUMBER() OVER(PARTITION BY 工号,日期,分组 ORDER BY 时间 ASC) AS 序号 FROM CC ORDER BY 工号
|
QQ好友和群
QQ空间
hessen