LokeshH says:
Please see the rows with ID 1 and 3.
In First Row Start Time is 11:10 AM and End Time is 12:20 PM and in Third Row Start Time is 12:10 PM and End Time is 01:00 PM that means overlap time is 10 Minutes manualy. But I want to calculate dynamicaly using SQL Query.
As per your data structure there is only start time and end time is saved it not with date.
So you need to first convert start time and end time with default date by converting it with date.
then you can use cross join with same table but in where condition check the same row will be ignore by his any primary key value matching with same cross join table . Here the Id seems the primary key so use it for checking.
Then you need to check the start time of second cross join table is in between start time and end time of first cross join table if it is in between then use date difference function to calculate the time difference.
SQL
DECLARE @Tabel AS TABLE(ID INT, DID INT, StartTime VARCHAR(50),EndTime VARCHAR(50))
INSERT INTO @Tabel VALUES(1,1,'11:10 AM','12:20 PM')
INSERT INTO @Tabel VALUES(2,1,'04:10 PM','04:40 PM')
INSERT INTO @Tabel VALUES(3,2,'12:10 PM','01:00 PM')
INSERT INTO @Tabel VALUES(4,2,'04:20 PM','04:55 PM')
SELECT a.StartTime 'First Start time'
,a.EndTime 'First End Time'
,b.StartTime 'Second start time'
,b.EndTime 'Second End Time'
,DATEDIFF(mi,(CONVERT(DATETIME,b.StartTime)),(CONVERT(DATETIME,a.EndTime))) AS 'Overlap Time Diff'
FROM @Tabel b,@Tabel a
where (a.ID <> b.ID)
AND (CONVERT(DATETIME,b.StartTime)) BETWEEN (CONVERT(DATETIME,a.StartTime)) AND (CONVERT(DATETIME,a.EndTime))
OUTPUT
First Start time |
First End Time |
Second start time |
Second End Time |
Overlap Time Diff |
11:10 AM |
12:20 PM |
12:10 PM |
1:00 PM |
10 |
4:10 PM |
4:40 PM |
4:20 PM |
4:55 PM |
20 |