查询以返回基于当前日期的连续日期的行

Question

我有以下问题要解决。我在SQL Server 2008中有一个表，其中包含以下列：

Person, JobType, ShiftDate, Hours

我需要提取任何已经工作(或计划工作(超过 7 个连续轮班的人(或任何已经工作或计划工作超过 52 小时的人(，今天是我的起点。

注意：人们每天可以工作1个以上的班次。因此，我有一个查询，可以选择今天之前的 7 天和未来的 7 天：

t1.Starton BETWEEN DATEADD(DAY, -7, GETDATE()) AND DATEADD(DAY, 7, GETDATE())

我的想法是，如果我可以选择任何连续的日期范围，今天位于"中间"，这至少会给我一个起点(因为任何缺少的日期都被认为是休息日并且不计算在内(？我在SO上看到了各种帖子，这些帖子显示了如何从表中获取连续的日期范围，但我不知道如何以今天为起点进行制作。任何关于更好方法的帮助或建议将不胜感激。

Answer 1

所以你想找到那些在上周和下周至少有7天连续的。

您可以按每个人的连续天数组进行排名。
然后计算每个人和日期组的天数。

完成此操作后，只需选择具有 7 天或更长时间的日期组的人员。

例如，以下测试代码段：

--
-- Using a table variable for easy testing
--
declare @ShiftTable table (Person varchar(30), JobType varchar(8), ShiftDate datetime, [Hours] decimal(5,2));
--
-- Sample Data
--
insert into @ShiftTable (Person, JobType, ShiftDate, [Hours]) values
('Mike','Ast',GetDate()-5,7.5),('Mike','Ast',GetDate()-4,7.5),
('Mike','Ast',GetDate()-3,7.5),('Mike','Ast',GetDate()-2,7.5),('Mike','Ast',GetDate()-1,7.5),('Mike','Ast',GetDate(),7.5),('Mike','Ast',GetDate()+1,7.5),('Mike','Ast',GetDate()+2,7.5),
('Dave','Help',GetDate()-2,7),('Dave','Help',GetDate()-1,7),('Dave','Help',GetDate(),5),
('Pam','Cook',GetDate()-6,8),('Pam','Cook',GetDate()-6,8),('Pam','Cook',GetDate()-4.2,3),('Pam','Cook',GetDate()-4,5),
('Pam','Cook',GetDate()-3.2,4),('Pam','Cook',GetDate()-3,3),('Pam','Cook',GetDate()-2,8),('Pam','Cook',GetDate()-1,8),
('Pam','Cook',GetDate(),8),('Pam','Cook',GetDate()+1,8),('Pam','Cook',GetDate()+3,8);
--
-- Query
--
SELECT DISTINCT Person
FROM
(
SELECT Person, DateGroup, COUNT(*) AS TotalDays
-- , MIN(ShiftDate) as MinShiftDate, MAX(ShiftDate) as MaxShiftDate
FROM
(
SELECT Person, CAST(ShiftDate AS DATE) AS ShiftDate,
DATEADD(day, ROW_NUMBER() OVER (PARTITION BY Person ORDER BY Person, CAST(ShiftDate AS DATE) DESC)-1, CAST(ShiftDate AS DATE)) as DateGroup
FROM @ShiftTable
WHERE CAST(ShiftDate AS DATE) BETWEEN CAST(DATEADD(DAY,-6,GETDATE()) AS DATE) AND CAST(GETDATE()+6 AS DATE)
GROUP BY Person, CAST(ShiftDate AS DATE)
) q1
GROUP BY Person, DateGroup
HAVING COUNT(*) >= 7
) q2
ORDER BY Person;

返回：

Person
------
Mike

Answer 2

--build a date table (you should build a permanent one)
select * into #DateTable from (SELECT dateadd(day,row_number() over (order by a1.name), cast('20180101' as date)) adate from sys.objects a1 cross join sys.objects b ) dq where dq.adate < '20220101'
--make some sample data
SELECT dq.* into #shifts from
(
select 1 as empid, cast('20180901 10:00:00' as datetime2) as starton, cast('20180901 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180902 10:00:00' as datetime2) as starton, cast('20180902 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180903 10:00:00' as datetime2) as starton, cast('20180903 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180904 10:00:00' as datetime2) as starton, cast('20180904 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180904 15:00:00' as datetime2) as starton, cast('20180904 19:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180905 10:00:00' as datetime2) as starton, cast('20180905 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180906 10:00:00' as datetime2) as starton, cast('20180906 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180907 10:00:00' as datetime2) as starton, cast('20180907 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180908 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180910 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180911 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180912 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180913 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180914 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180915 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180916 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180917 0:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180919 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn
union
select 1 as empid, cast('20180919 10:00:00' as datetime2) as starton, cast('20180908 12:00:00' as datetime2) as endOn


) dq;

--here is the query - find days that are followed by 6 days with shifts in the next 6 days (so shifts that are part of a 7 day run)
SELECT IQ.empid, IQ.starton dayFollowedBy6Shifts from 
(
select s.empid,CASt(s.starton as date) starton, cast(s2.starton as date) starton2 from #shifts s join #DateTable d on  DATEDIFF(DAY, s.starton, d.adate) < 7 and d.adate >= CAST(s.starton as date) join #shifts s2 on  s2.empid = s.empid and cast(s2.starton as date) = d.adate
Group by s.empid, CASt(s.starton as date), cast(s2.starton as date)
) IQ 
group by IQ.empid,IQ.starton having COUNT(0) >= 7


drop table #datetable;
drop table #shifts;