如果一个员工ID对应一天内多条打卡数据,数据格式是‘年月日 小时分秒’,如何把这一列值从varchar变成时间格式的days,并且计算同一个员工ID任意最近相隔两天的打卡记录?非常感谢
sample data 如下
BadgeID location BadgeReadingTime Name
100010 Beijing "02/04/2016 12:09AM" Liming
100010 Beijing "02/04/2016 7:20PM" Liming
100010 Beijing "02/07/2016 08:09AM" Liming
100020 Beijing "02/010/2016 08:00AM" Zhanglu
100030 Shanghai "02/07/2016 06:18AM" Liming
原始数据是在txt里面 我导入mysql之后 时间就是都带引号的 现在问题是1.先需要转换时间格式 2. 同一个ID同一天有多条记录的话只选择最早的一条 3. 员工不是每天上班的,所以同一个ID要计算所有最近不连续的相邻日之间天数间隔
最好能麻烦帮我写个sample 代码
2 个解决方案
#1
用的mssql写的,mysql和这个差不多,函数用法可能有点不一样,参考一下
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([BadgeID] int,[location] nvarchar(28),[BadgeReadingTime] NVARCHAR(100),[Name] nvarchar(27))
Insert #T
select 100010,N'Beijing','"02/04/2016 12:09AM"',N'Liming' union all
select 100010,N'Beijing','"02/04/2016 7:20PM"',N'Liming' union all
select 100010,N'Beijing','"02/07/2016 08:09AM"',N'Liming' union all
select 100020,N'Beijing','"02/010/2016 08:00AM"',N'Zhanglu' union all
select 100030,N'Shanghai','"02/07/2016 06:18AM"',N'Liming'
Go
--测试数据结束
--1、
UPDATE #T SET BadgeReadingTime = REPLACE(BadgeReadingTime,'"','')
SELECT BadgeID ,
location ,
CONVERT(DATETIME, BadgeReadingTime) AS BadgeReadingTime ,
Name
FROM #T;
--2、
SELECT a.*
FROM #T a
JOIN ( SELECT BadgeID ,
MIN(CONVERT(DATETIME, BadgeReadingTime)) AS BadgeReadingTime
FROM #T
GROUP BY BadgeID
) t ON t.BadgeID = a.BadgeID
AND t.BadgeReadingTime = CONVERT(DATETIME,a.BadgeReadingTime)
--3、
SELECT t.BadgeID ,
DATEDIFF(DAY, t.BadgeReadingTime, t1.BadgeReadingTime) AS 天数
FROM ( SELECT BadgeID ,
location ,
CONVERT(DATETIME, BadgeReadingTime) AS BadgeReadingTime ,
Name ,
ROW_NUMBER() OVER ( ORDER BY BadgeID, CONVERT(DATETIME, BadgeReadingTime) ) AS num
FROM #T
) t
JOIN ( SELECT BadgeID ,
location ,
CONVERT(DATETIME, BadgeReadingTime) AS BadgeReadingTime ,
Name ,
ROW_NUMBER() OVER ( ORDER BY BadgeID, CONVERT(DATETIME, BadgeReadingTime) ) AS num
FROM #T
) t1 ON t1.BadgeID = t.BadgeID
AND t1.num - 1 = t.num
WHERE DATEDIFF(DAY, t.BadgeReadingTime, t1.BadgeReadingTime) > 1;

#2
非常感谢
我试了一下,代码还是有些问题,第二个步骤中实际需要选择同一天内的最小值,而不是同一ID下所有记录的最小值,然后在算datediff的时候要在最相邻的两个天数中循环比较,求帮助。。。
另外如果在计算间隔天数的话需要把另一个表中节假日和周末都去掉怎么处理呢
我试了一下,代码还是有些问题,第二个步骤中实际需要选择同一天内的最小值,而不是同一ID下所有记录的最小值,然后在算datediff的时候要在最相邻的两个天数中循环比较,求帮助。。。
另外如果在计算间隔天数的话需要把另一个表中节假日和周末都去掉怎么处理呢
#1
用的mssql写的,mysql和这个差不多,函数用法可能有点不一样,参考一下
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([BadgeID] int,[location] nvarchar(28),[BadgeReadingTime] NVARCHAR(100),[Name] nvarchar(27))
Insert #T
select 100010,N'Beijing','"02/04/2016 12:09AM"',N'Liming' union all
select 100010,N'Beijing','"02/04/2016 7:20PM"',N'Liming' union all
select 100010,N'Beijing','"02/07/2016 08:09AM"',N'Liming' union all
select 100020,N'Beijing','"02/010/2016 08:00AM"',N'Zhanglu' union all
select 100030,N'Shanghai','"02/07/2016 06:18AM"',N'Liming'
Go
--测试数据结束
--1、
UPDATE #T SET BadgeReadingTime = REPLACE(BadgeReadingTime,'"','')
SELECT BadgeID ,
location ,
CONVERT(DATETIME, BadgeReadingTime) AS BadgeReadingTime ,
Name
FROM #T;
--2、
SELECT a.*
FROM #T a
JOIN ( SELECT BadgeID ,
MIN(CONVERT(DATETIME, BadgeReadingTime)) AS BadgeReadingTime
FROM #T
GROUP BY BadgeID
) t ON t.BadgeID = a.BadgeID
AND t.BadgeReadingTime = CONVERT(DATETIME,a.BadgeReadingTime)
--3、
SELECT t.BadgeID ,
DATEDIFF(DAY, t.BadgeReadingTime, t1.BadgeReadingTime) AS 天数
FROM ( SELECT BadgeID ,
location ,
CONVERT(DATETIME, BadgeReadingTime) AS BadgeReadingTime ,
Name ,
ROW_NUMBER() OVER ( ORDER BY BadgeID, CONVERT(DATETIME, BadgeReadingTime) ) AS num
FROM #T
) t
JOIN ( SELECT BadgeID ,
location ,
CONVERT(DATETIME, BadgeReadingTime) AS BadgeReadingTime ,
Name ,
ROW_NUMBER() OVER ( ORDER BY BadgeID, CONVERT(DATETIME, BadgeReadingTime) ) AS num
FROM #T
) t1 ON t1.BadgeID = t.BadgeID
AND t1.num - 1 = t.num
WHERE DATEDIFF(DAY, t.BadgeReadingTime, t1.BadgeReadingTime) > 1;

#2
非常感谢
我试了一下,代码还是有些问题,第二个步骤中实际需要选择同一天内的最小值,而不是同一ID下所有记录的最小值,然后在算datediff的时候要在最相邻的两个天数中循环比较,求帮助。。。
另外如果在计算间隔天数的话需要把另一个表中节假日和周末都去掉怎么处理呢
我试了一下,代码还是有些问题,第二个步骤中实际需要选择同一天内的最小值,而不是同一ID下所有记录的最小值,然后在算datediff的时候要在最相邻的两个天数中循环比较,求帮助。。。
另外如果在计算间隔天数的话需要把另一个表中节假日和周末都去掉怎么处理呢