Time duration calculation ignoring overlaps

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Time duration calculation ignoring overlaps

Author

Topic

narengk
Starting Member

7 Posts

Posted - 2008-04-16 : 23:17:42

Would like to know if it is possible to calculate the duration of a Datetime Start and End Dates ignoring all overlapps?
Eg:
1) StartTime 10:00:00 EndTime 11:00:00 Duration: 01:00:00
2) StartTime 10:30:00 EndTime 11:15:00 Duration: 00:45:00
Total Duration should be 01:15:00 and not 01:45:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 23:59:37

[code]DECLARE @TimeTable Table
(
ID int identity(1,1),
StartTime datetime,
EndTime datetime
)
INSERT INTO @TimeTable
SELECT '10:00','11:00'
UNION ALL
SELECT '10:30','11:15'
UNION ALL
SELECT '11:30','11:45'
UNION ALL
SELECT '11:30','12:15'

select SUM(DATEDIFF(n,t1.StartTime,t1.EndTime) + CASE WHEN DATEDIFF(n,t1.EndTime,t2.StartTime) <0 THEN DATEDIFF(n,t1.EndTime,t2.StartTime) ELSE 0 END)
FROM @TimeTable t1
LEFT JOIN @TimeTable t2
ON t1.ID=t2.ID-1[/code]

narengk
Starting Member

7 Posts

Posted - 2008-04-17 : 03:21:46

Thank you very much , How do I retrieve from the existing table ? Should I use a Table variable ? If Yes , How Can I use UNION ALL.
OR should I use Cursors and Pass One after other to the Select Statements ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 03:24:00

The first part is only to mimic your environment.
We don't sit at your office and have access to your data, right?

This is the solution for you.

select SUM(DATEDIFF(n,t1.StartTime,t1.EndTime) + CASE WHEN DATEDIFF(n,t1.EndTime,t2.StartTime) <0 THEN DATEDIFF(n,t1.EndTime,t2.StartTime) ELSE 0 END)
FROM {YourTableNameHere} t1
LEFT JOIN {YourTableNameHere} t2
ON t1.ID=t2.ID-1

E 12°55'05.25"
N 56°04'39.16"

narengk
Starting Member

7 Posts

Posted - 2008-04-17 : 04:35:40

Thank you so much.

narengk
Starting Member

7 Posts

Posted - 2008-04-17 : 06:36:15

Can you please let me know if i am wrong ?

DECLARE @TimeTable Table
(
ID int identity(1,1),
StartTime datetime,
EndTime datetime
)
INSERT INTO @TimeTable
SELECT '07:12','07:17' --00:05
UNION ALL
SELECT '07:18','07:24' --00:06
UNION ALL
SELECT '07:33','07:34' --00:01
UNION ALL
SELECT '07:34','08:02' --00:28
UNION ALL
SELECT '07:34','07:43' --00:00
UNION ALL
SELECT '07:43','07:49' --00:00
UNION ALL
SELECT '07:53','08:01' --00:00
UNION ALL
SELECT '07:57','08:10' --00:13

select SUM(DATEDIFF(n,t1.StartTime,t1.EndTime) + CASE WHEN DATEDIFF(n,t1.EndTime,t2.StartTime) <0 THEN DATEDIFF(n,t1.EndTime,t2.StartTime) ELSE 0 END)
FROM @TimeTable t1
LEFT JOIN @TimeTable t2
ON t1.ID=t2.ID-1
--Result Obtained is 00:44 where as actually it is 00:53

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 06:58:02

Your explanation is not consistent.

How come last record (07:57-08:10) gets full 13 minutes when still overlapping the (07:34-08:02) record?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 06:59:36

[code]DECLARE @Sample TABLE (StartTime DATETIME, EndTime DATETIME)

INSERT @Sample
SELECT '07:12', '07:17' UNION ALL --00:05
SELECT '07:18', '07:24' UNION ALL --00:06
SELECT '07:33', '07:34' UNION ALL --00:01
SELECT '07:34', '08:02' UNION ALL --00:28
SELECT '07:34', '07:43' UNION ALL --00:00
SELECT '07:43', '07:49' UNION ALL --00:00
SELECT '07:53', '08:01' UNION ALL --00:00
SELECT '07:57', '08:10' --00:13

SELECT COUNT(DISTINCT v.Number) AS Minutes
FROM master..spt_values AS v
INNER JOIN (
SELECT DATEDIFF(MINUTE, '00:00:00', StartTime) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', EndTime) AS EndMinute
FROM @Sample
) AS x ON x.StartMinute <= v.Number
AND x.EndMinute > v.Number
WHERE v.Type = 'p'
AND Number < 1440[/code]The result is 48 minutes.

E 12°55'05.25"
N 56°04'39.16"

narengk
Starting Member

7 Posts

Posted - 2008-04-17 : 07:01:25

Extreemly sorry guys , Fixed the last entry , also the calculation difference in my earlier procedure and this was that I used seconds and not minutes and hence was deviating by a small %.

Thanks any way. and sorry again.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-17 : 07:53:58

Interesting to read this ,
Let me know how can the table be passed in the second solution (using master..spt_values)This would be very valuable to me.
Can any one expalin why there sould be difference between the two methods discussed here.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:55:12

Visakh doesn't take into account that there could be multiple overlappings.

E 12°55'05.25"
N 56°04'39.16"

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-17 : 07:59:14

Hi Peso,
How can we pass the table to your second method, It returns value 0 when I replace @sample with my table ?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-17 : 08:35:06

Can any one help ?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-17 : 23:12:31

If the table Column is of Datetime ,
We need to replace StartTime with Convert(varchar(10),StartTime,108)
Peter,
Can you help me in checking for the entire date than Time alone ? Say

If the sample table is :
INSERT @Sample
SELECT '2008.04.16 23:53:00', '2008.04.17 00:19:00' UNION ALL
SELECT '2008.04.16 23:53:00', '2008.04.17 00:01:00' UNION ALL
SELECT '2008.04.16 23:57:00', '2008.04.17 00:10:00'

Instead of Time alone,This query returns wrong results when dates are not checked.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-18 : 02:26:35

Hello Masters of Master Spt_Values! Peter/Harsh.....

Can you help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 02:47:28

What is the expected output form the sample data you provided?

E 12°55'05.25"
N 56°04'39.16"

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-18 : 03:29:15

Hi Peter,

The problem is the same started by narengk , only difference is that we need to include date and time instaed of time only.

In the below example RPMS.dbo.List_PaintShopAlarms has StartDate instead of StartTime and EndDate instead of Endtime

SELECT COUNT(DISTINCT v.Number) AS Minutes
FROM master..spt_values AS v
INNER JOIN (
SELECT DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute
FROM RPMS.dbo.List_PaintShopAlarms
) AS x ON x.StartMinute <= v.Number

AND x.EndMinute > v.Number

WHERE v.Type = 'p'
AND Number < 1440

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources