CODEWITHSUNDEEP
sql-server
Bev0
Bev0

Reputation: 3

Time and Date Clash - Sql Server

stuck on a project. I wrote this code in sql server which finds the duplicate date matches for a staff member, but I'm stuck when trying to expand it to narrow it down to when the time ranges overlap each other also.

So there is a table called 'Rosters' with columns 'StaffID', 'Date', 'Start', '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

It returns all duplicate dates for each staff member, I wish to add the logic-

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

Is it possible with the way I'm currently doing it? Any help would be appreciated.

@TT. Sorry, below is probably a better visual explanation -

e.g This would be the roster table

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

And I'm trying to return what is below for the example as they are the only 2 rows that clash for ID, Date, and the Time range (start - end)

ID      Date     Start    End

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

Upvotes: 0

Views: 508

Answers (3)

Nolan Shang
Nolan Shang

Reputation: 2328

If you use SQL Server 2012 up, you can try below script:

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

Upvotes: 0

iamdave
iamdave

Reputation: 12243

This is the logic that you would need to filter your results to overlapping time ranges, though I think this can be handled without your intermediate step of finding the duplicates. If you simply post your source table schema with some test data and your desired output, you will get a much better answer:

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

Upvotes: 2

Munavvar
Munavvar

Reputation: 821

Try this

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

Made some changes in @iamdave's Answer

Upvotes: 1

Related Questions