时间和日期冲突 - Sql Server

问题描述：

卡住了一个项目。我在sql服务器中发现了这个代码，它发现了一个职员的重复日期匹配，但是当我试图扩展它以缩小它到时间范围也彼此重叠时，我被卡住了。时间和日期冲突 - Sql Server

因此，有一个叫做“花名册”同列“ STAFFID”，“日期”，“开始”，“终结”

SELECT 
y.[Date],y.StaffID,y.Start,y.[End] 
FROM Rosters y 
INNER JOIN (SELECT 
[Date],StaffID, COUNT(*) AS CountOf 
FROM Rosters 
GROUP BY [Date],StaffID 
HAVING COUNT(*)>1) 
dd ON y.[Date]=dd.[Date] and y.StaffID=dd.StaffID

它返回所有重复日期为每个工作人员的表，我想添加逻辑 -

y.Start <= dd.[End] && dd.Start <= y.[End]

这是可能的方式我目前正在做呢？任何帮助，将不胜感激。

@TT。抱歉，下面可能是一个更好的视觉的解释 -

例如，这将是名册表

ID  Date  Start End

1 01/01/2000 8:00 12:00 
1 01/01/2000 9:00 11:00 
2 01/01/2000 10:00 14:00 
2 01/01/2000 8:00 9:00 
3 01/01/2000 14:00 18:00 
3 02/02/2002 13:00 19:00

而且我试图返回下面究竟是什么的例子，因为他们是对ID，日期和时间范围冲突的唯一2行（开始 - 结束）

ID  Date  Start End

1 01/01/2000 8:00 12:00 
1 01/01/2000 9:00 11:00

dd子查询可能包含多个开始和结束，您想要比较哪个值？ –

你应该用单词而不是T-SQL解释你想要的。不要专注于解决方案，而是解释你想要做什么。 –

@NolanShang - 我试图把它包括在子查询中，但它不返回任何东西，我有一种感觉，我正在做一些小错误，但它是我画空白的那些东西之一:( – Bev0

答

试试这个

with cte as 
(
    SELECT ROW_NUMBER() over (order by StaffID,Date,Start,End) as rno 
    ,StaffID, Date, Start, End 
    FROM Rosters 
) 
select distinct t1.* 
from cte t1 
inner join cte t2 
on(t1.rno <> t2.rno 
    and t1.StaffID = t2.StaffID 
    and t1.Date = t2.Date 
    and t1.Start <= t2.End 
    and t1.End >= t2.Start 
    ) 
order by t1.rno

制造@ iamdave的回答有些变化

谢谢，这由于iamdave的解决方案中没有提到的唯一ID，因此证明rno的加入是有用的。是否可以将结果分组在一行中？ – Bev0

答

这是您需要将结果过滤为重叠时间范围的逻辑，尽管我认为这可以在没有找到重复的中间步骤的情况下处理。如果你只是用一些测试数据和所需输出张贴您的源表的模式，你会得到一个更好的答案：

declare @t table (RowID int 
        ,ID int 
        ,DateValue date  --\ 
        ,StartTime Time  -- > Avoid using reserved words for your object names. 
        ,EndTime Time  --/ 
        ); 
insert into @t values 
(1,1, '01/01/2000', '8:00','12:00') 
,(2,1, '01/01/2000', '9:00','11:00') 
,(3,2, '01/01/2000', '10:00','14:00') 
,(4,2, '01/01/2000', '8:00','9:00' ) 
,(5,3, '01/01/2000', '14:00','18:00') 
,(6,3, '02/02/2002', '13:00','19:00'); 

select t1.* 
from @t t1 
    inner join @t t2 
     on(t1.RowID <> t2.RowID  -- If you don't have a unique ID for your rows, you will need to specify all columns so as no to match on the same row. 
      and t1.ID = t2.ID 
      and t1.DateValue = t2.DateValue 
      and t1.StartTime <= t2.EndTime 
      and t1.EndTime >= t2.StartTime 
      ) 
order by t1.RowID

答

如果使用SQL Server 2012中，你可以试试下面的脚本：

declare @roster table (StaffID int,[Date] date,[Start] Time,[End] Time); 
insert into @roster values 
(1, '01/01/2000', '9:00','11:00') 
,(1, '01/01/2000', '8:00','12:00') 
,(2, '01/01/2000', '10:00','14:00') 
,(2, '01/01/2000', '8:00','9:00' ) 
,(3, '01/01/2000', '14:00','18:00') 
,(3, '02/02/2002', '13:00','19:00'); 
SELECT t.StaffID,t.Date,t.Start,t.[End] FROM (
    SELECT y.StaffID,y.Date,y.Start,y.[End] 
    ,CASE WHEN y.[End] BETWEEN 
    LAG(y.Start)OVER(PARTITION BY y.StaffID,y.Date ORDER BY y.Start) AND LAG(y.[End])OVER(PARTITION BY y.StaffID,y.Date ORDER BY y.Start) THEN 1 ELSE 0 END 
    +CASE WHEN LEAD(y.[End])OVER(PARTITION BY y.StaffID,y.Date ORDER BY y.Start) BETWEEN y.Start AND y.[End] THEN 1 ELSE 0 END AS IsOverlap 
    ,COUNT (0)OVER(PARTITION BY y.StaffID,y.Date) AS cnt 
    FROM @roster AS y 
) t WHERE t.cnt>1 AND t.IsOverlap>0

 
StaffID  Date  Start   End 
----------- ---------- ---------------- ---------------- 
1   2000-01-01 08:00:00.0000000 12:00:00.0000000 
1   2000-01-01 09:00:00.0000000 11:00:00.0000000

时间和日期冲突 - Sql Server

相关推荐