SQL Query, Get list of range conflicts

Question

I need help creating my query and would be grateful if someone could help.

I have got a staff time entry website. The website saves the start and end times of their work.

They can enter a few different times on the same day as long as these times are not overclocking other than their entered times.

For example, if they clocked from 08:00 AM to 12:00 PM. Their second entry mustn't be between these times.

I have a formula which checks two different ranges.

Max(start1,start2) < Min(end1,end2)

So, I would like to implement this formula in my database.

for Example

EmployeeID	SelectedDate	StartTimeValue	EndTimeValue
1	2022-10-10	1	10
1	2022-10-10	15	20
1	2022-10-10	25	37
1	2022-10-10	35	40
1	2022-10-10	11	21

In the above entery last two is overclocking.

35 - 40 entry is worng because 35 is between 25-37, which is previously entered. The 11- 21 entry is wrong because it covers the 15-20.

BASICALLY, I would like to know how to implement this formula in SQL Max(start1,start2) < Min(end1,end2)

I Tried something like this but it didn't work

SELECT  
  T1.EmployeeID,T1.SelectedDate,T1.StartTimeValue,T1.EndTimeValue,
  T2.EmployeeID,T2.SelectedDate,T2.StartTimeValue,T2.EndTimeValue
FROM [adilsari_Test].[dbo].[EmployeeTimeSheetModel] T1
INNER JOIN [adilsari_Test].[dbo].[EmployeeTimeSheetModel] T2 ON T1.EmployeeID = t2.EmployeeID 
 AND T1.TimeSheetID <> t2.TimeSheetID 
 AND  T1.SelectedDate = T2.SelectedDate
WHERE (SELECT MAX(v) FROM(VALUES (T1.StartTimeValue,T2.StartTimeValue)) AS value(v))>10 AND MIN(T1.EndTimeValue,T2.EndTimeValue)

Can you include what your expected output would be? – Sam Cohen-Devries Jan 03 '23 at 16:42 — Sam Cohen-Devries, Jan 03 '23 at 16:42
Can you include why "... but it didn't work" ? – Luuk Jan 03 '23 at 16:43 — Luuk, Jan 03 '23 at 16:43

score 1 · Accepted Answer · answered Jan 03 '23 at 16:58

I think you are looking for overlaping ranges, in this case you can refer to Determine Whether Two Date Ranges Overlap, a query would be like :

DROP TABLE IF EXISTS #Temp

SELECT 1 EmployeeID,'2022-10-10'    SelectedDate,1  StartTimeValue,10   EndTimeValue
INTO #Temp
UNION ALL SELECT 1,'2022-10-10',15, 20
UNION ALL SELECT 1,'2022-10-10',25, 37
UNION ALL SELECT 1,'2022-10-10',35, 40
UNION ALL SELECT 1,'2022-10-10',11, 21

SELECT *
FROM #Temp t
JOIN #Temp t1 ON t.SelectedDate = t1.SelectedDate AND t.StartTimeValue <> t1.StartTimeValue AND t.EndTimeValue <> t1.EndTimeValue
WHERE (t.StartTimeValue <= t1.EndTimeValue)  AND  (t.EndTimeValue >= t1.StartTimeValue)

SQL Query, Get list of range conflicts

1 Answers1