Consolidating contiguous time periods

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 2000 Forums

Transact-SQL (2000)

Consolidating contiguous time periods

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-10 : 07:54:17

Adam writes "Hello. This is a rough one that's been wrecking my head for too long now (and a deadline looms).

I have a table called Overtime, whose rows represent time periods. It has two columns of relevance here:

Start datetime NOT NULL,
Finish datetime NOT NULL

I need a query that will consolidate any contiguous time periods into one.

i.e. if the table has the following four rows:

2003-11-17 17:00:00, 2003-11-17 18:30:00
2003-11-17 18:30:00, 2003-11-17 20:00:00
2003-11-17 20:00:00, 2003-11-18 00:00:00
2003-11-18 17:00:00, 2003-11-18 18:30:00

the top three rows will be consolidated into one, so we should get something like this:

2003-11-17 17:00:00, 2003-11-18 00:00:00
2003-11-18 17:00:00, 2003-11-18 18:30:00

Any ideas would be appreciated! Thanks."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-10 : 10:02:11

[code]create table overtime (dtstart datetime,dtend datetime)

insert into overtime
select '2003-11-17 17:00:00', '2003-11-17 18:30:00' union all
select '2003-11-17 18:30:00', '2003-11-17 20:00:00' union all
select '2003-11-17 20:00:00', '2003-11-18 00:00:00' union all
select '2003-11-18 17:00:00', '2003-11-18 18:30:00'

select min(os.dtstart) dtstart,max(os.dtend) dtend
from overtime os
group by convert(datetime,cast(os.dtstart as varchar(12)))
[/code]

HendersonToo
Starting Member

20 Posts

Posted - 2004-02-11 : 18:03:47

Does it matter if the datetime's aren't completely contiguous... What's the specified behavior if there is a gap of time between dtend and the next dtstart? Beware the posted solution ignores gaps.

You'll need to define for yourself how much time can pass between dtend and the next dtstart, and still be considered contiguous and then adjust the group by accordingly. Also, add an 'order by dtstart'.

almo
Starting Member

1 Post

Posted - 2004-03-17 : 20:07:08

I want to do something similar. A user can request vacation in my app in daily increments of 8 hours, no holidays or weekends allowed. But if the user requests 8 hours on '3/26/04' and 8 hours on '3/29/04', I want the result set to appear as one contiguous summed row, e.g., 2004-03-26, 2004-03-29, 16 hours .... any ideas?? Cursor??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources