Get min() and max() of datetimes

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)

Get min() and max() of datetimes

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-17 : 09:07:00

create table #t (t1 datetime, t2 datetime, r int)
insert into #t
select '2003-11-10 05:05:00', '2003-11-10 05:30:00', NULL union all
select '2003-11-10 05:30:00', '2003-11-10 05:55:00', NULL union all
select '2003-11-10 05:55:00', '2003-11-10 06:20:00', NULL union all
select '2003-11-10 06:20:00', '2003-11-10 06:45:00', NULL union all
select '2003-11-10 06:45:00', '2003-11-10 07:10:00', NULL union all
select '2003-11-10 07:10:00', '2003-11-10 07:35:00', NULL union all
select '2003-11-10 07:35:00', '2003-11-10 08:00:00', NULL union all
select '2003-11-10 10:05:00', '2003-11-10 10:30:00', NULL union all
select '2003-11-10 10:30:00', '2003-11-10 10:55:00', 5742 union all
select '2003-11-10 10:55:00', '2003-11-10 11:20:00', 5751 union all
select '2003-11-10 11:20:00', '2003-11-10 11:45:00', 5751 union all
select '2003-11-10 11:45:00', '2003-11-10 12:10:00', 5751 union all
select '2003-11-10 12:10:00', '2003-11-10 12:35:00', NULL union all
select '2003-11-10 12:35:00', '2003-11-10 13:00:00', NULL union all
select '2003-11-10 13:00:00', '2003-11-10 13:25:00', NULL union all
select '2003-11-10 16:20:00', '2003-11-10 16:45:00', 3991 union all
select '2003-11-10 16:20:00', '2003-11-10 16:45:00', 5742

Get mint1, maxt2 for each span of r=NULL when table is ordered by t1, t2, r.


mint1                       maxt2
--------------------------- --------------------------- 
2003-11-10 05:05:00.000     2003-11-10 10:30:00.000
2003-11-10 12:10:00.000     2003-11-10 13:25:00.000

LOL

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-17 : 09:52:37

Stoad,

Are you looking for a set-based solution??

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-17 : 09:58:24

Of course. Plus, have you noticed my LOL at the end?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-17 : 10:48:59

You can apply my famous (

) "runs and streaks" technique fairly easily:

select min(t1), max(t1)
FROM
(
select a.*, (select count(*) from #t b where b.t1 <= a.t1 and b.r is not null) as Grp
from #t a
where a.r is null
) a
group by Grp

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-17 : 11:05:58

Very cool Jeff !!

A slight variation to retrieve maxt2