时间和日期冲突-SQL Server

Question

卡在项目上。我在SQL Server中编写了此代码，该代码找到了工作人员的重复日期匹配，但是当试图将其扩展以将其缩小到时间范围时，我仍然卡住了。

。 。

因此，有一张名为"花名册"，带有列" sextid"，" date"，" start"，" end'end'

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

，我正在尝试返回以下示例的内容，因为它们是唯一的2行发生冲突，日期和时间范围（start -end）

ID      Date     Start    End

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

Answer 1

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

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

Answer 2

尝试此

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的答案进行了一些更改

Answer 3

如果您使用SQL Server 2012 UP，则可以尝试以下脚本：

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日期开始结束-----------------------------------------------------------------------------1 2000-01-01 08：00：00.0000000 12：00：00.00000001 2000-01-01 09：00：00.0000000 11：00：00.0000000