时间戳

首页 > 解决方案 > SQL-员工休假日期跟踪

sql - SQL-员工休假日期跟踪

问题描述

Emp id start date end date 
1. 1/14/18. 1/4/18 
1. 1/8/18. 1/8/18 
1. 1/11/18. 1/11/18 
1. 1/12/18. 1/12/18 
1. 1/13/18. 1/13/18 
1. 1/14/18. 1/14/18 
1. 1/15/18. 1/15/18 
1. 1/16/18. 1/16/18 
2. 1/1/18 1/13/18

我需要如果员工 #1 从上表持续超过 5 天，我需要收到警报。所以从第 3 行到第 8 行应该算。前两行不应该计算在内。请在sql中提供帮助。可能正在考虑创建循环。

标签： sql

解决方案

谢谢戈登。它确实有效，但现在我想使用游标将此查询放入循环中，以便它将按员工 ID 读取每条记录。我确实放了，但同样的 6 条记录在重复。我认为我声明的循环有问题：

DECLARE @emp as varchar(20);
Declare @start as Date;
Declare @end as Date;
--Declare @date as Date;
DECLARE pstar2 CURSOR FOR
SELECT ApproveTime.[Employee ID],ApproveTime.[Start Date],ApproveTime.[End Date] 
from ApproveTime 
--where ApproveTime.[Employee ID]='JUF0036419' and
--ApproveTime.[Start Date]=ApproveTime.[End Date] 
--ApproveTime.[Employee ID]='36419'

open pstar2
--while loop
--EXIT when c1%NOTFOUND;
 FETCH next from pstar2 into @emp, @start, @end
 while (@@FETCH_STATUS=0)
 begin
select 
       count(*) over (partition by ApproveTime.[Employee ID], grp) as cnt, *
from (select t.*, 
             (t.[Start Date] -
              row_number() over (partition by t.[Employee ID] order by t.[Start Date]) * 1
             ) as grp
      from approvetime t
     ) approvetime where ApproveTime.[Employee ID]='36419'
     and  (ApproveTime.[Start Date] >='2018-01-01 00:00:00.000' and ApproveTime.[End Date]<='2018-01-31 00:00:00.000')
 print @emp 
 print @start`enter code here`
 print @end

 end
 --end
close pstar2
DEALLOCATE pstar2

推荐阅读