我有一张员工时钟打卡表,看起来像这样:
| EmployeeID | PunchDate | PunchTime | PunchType | Sequence |
|------------|------------|-----------|-----------|----------|
| 5386 | 12/27/2016 | 03:57:42 | On Duty | 552 |
| 5386 | 12/27/2016 | 09:30:00 | Off Duty | 563 |
| 5386 | 12/27/2016 | 010:02:00 | On Duty | 564 |
| 5386 | 12/27/2016 | 12:10:00 | Off Duty | 570 |
| 5386 | 12/27/2016 | 12:22:00 | On Duty | 571 |
| 5386 | 12/27/2016 | 05:13:32 | Off Duty | 578 |
我需要做的是删除任何值班时间与随后的 值班时间之间的分钟差少于25分钟的行。在上面的示例中,我想删除序列570和571。
我已经通过拉动所有创建此表的关税从另一个表拳和使用此查询拉所有值班遵循的拳退出值班冲:
SELECT * FROM [dbo].[Punches]
INSERT INTO [dbo].[UpdatePunches (EmployeeID,PunchDate,PunchTime,PunchType,Sequence)
SELECT * FROM [dbo].[Punches]
WHERE Sequence IN (
SELECT Sequence + 1
FROM [dbo].[Punches]
WHERE PunchType LIKE 'Off Duty%') AND
PunchType LIKE 'On Duty%'
我一直在尝试在此代码中以及作为清除这些错误的一个单独步骤来适合某种DATEDIFF查询,但是还没有任何运气。我不能使用特定的序列号,因为每次打孔时这些序列号都会改变。
我正在使用SQL Server 2008。
任何建议将不胜感激。
您可以根据打孔日期和打孔时间为每个员工分配行号,并根据日期和时间的升序将每一行与下一行连接起来。
此后,获取相差少于25分钟的那些行的行号,最后删除这些行。
with rownums as
(select t.*,row_number() over(partition by employeeid
order by cast(punchdate +' '+punchtime as datetime) ) as rn
from t)
,rownums_to_delete as
(
select r1.rn,r1.employeeid
from rownums r1
join rownums r2 on r1.employeeid=r2.employeeid and r1.rn=r2.rn+1
where dateadd(minute,25,cast(r2.punchdate +' '+r2.punchtime as datetime)) > cast(r1.punchdate +' '+r1.punchtime as datetime)
and r1.punchtype <> r2.punchtype
union all
select r2.rn, r2.employeeid
from rownums r1
join rownums r2 on r1.employeeid=r2.employeeid and r1.rn=r2.rn+1
where dateadd(minute,25,cast(r2.punchdate +' '+r2.punchtime as datetime)) > cast(r1.punchdate +' '+r1.punchtime as datetime)
and r1.punchtype <> r2.punchtype
)
delete r
from rownums_to_delete rd
join rownums r on rd.employeeid=r.employeeid and r.rn=rd.rn
如果日期和时间列不是varchar
实际列date
和time
数据类型,请punchdate+punchtime
在查询中使用。
编辑:更简单的查询版本将是
with todelete as (
select t1.employeeid,cast(t2.punchdate+' '+t2.punchtime as datetime) as punchtime,
t2.punchtype,t2.sequence,
cast(t1.punchdate+' '+t1.punchtime as datetime) next_punchtime,
t1.punchtype as next_punchtype,t1.sequence as next_sequence
from t t1
join t t2 on t1.employeeid=t2.employeeid
and cast(t2.punchdate+' '+t2.punchtime as datetime) between dateadd(minute,-25,cast(t1.punchdate+' '+t1.punchtime as datetime)) and cast(t1.punchdate+' '+t1.punchtime as datetime)
where t2.punchtype <> t1.punchtype
)
delete t
from t
join todelete td on t.employeeid = td.employeeid
and cast(t.punchdate+' '+t.punchtime as datetime) in (td.punchtime,td.next_punchtime)
;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句