Max & Min date ranges - SQL Server Forums

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)

Max & Min date ranges

Author

Topic

Sachin.Nand

2937 Posts

Posted - 2009-11-06 : 05:49:07

Hi guys,


declare @tbl as table(typ int,stdt datetime,enddt datetime,diff int)
insert into @tbl
select 1,'2009-08-12 08:01:00.000',	'2009-08-12 08:04:00.000',3 union all
select 1,'2009-08-12 08:01:00.000',	'2009-08-12 08:08:00.000',7 union all
select 1,'2009-08-12 08:04:00.000'	,'2009-08-12 08:08:00.000',4 union all
select 1,'2009-08-12 08:08:00.000',	'2009-08-12 08:18:00.000',10

select * from @tbl

i want that the resultset to be like this
1) 2009-08-12 08:01:00.000 and 2009-08-12 08:08:00.000
2) 2009-08-12 08:08:00.000 and 2009-08-12 08:18:00.000
because the difference between 2009-08-12 08:01:00.000 and 2009-08-12 08:08:00.000 is less than 10
and the difference between2009-08-12 08:08:00.000 and 2009-08-12 08:18:00.000 is 10

PBUH

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-06 : 07:56:18

This?

Select min(stdt),max(enddt)
from @tbl
where diff < 10
union all
Select min(stdt),max(enddt)
from @tbl
where diff >= 10

Jim

Everyday I learn something that somebody else already knew

Sachin.Nand

2937 Posts

Posted - 2009-11-06 : 08:23:18

quote:
Originally posted by jimf

This?

Select min(stdt),max(enddt)
from @tbl
where diff < 10
union all
Select min(stdt),max(enddt)
from @tbl
where diff >= 10

Jim

Everyday I learn something that somebody else already knew

The value 10 is configurable & can be anything.Now if we tk the below sample data
declare @tbl as table(typ int,stdt datetime,enddt datetime,diff int)
insert into @tbl
select 1,'2009-08-12 08:01:00.000', '2009-08-12 08:04:00.000',3 union all
select 1,'2009-08-12 08:01:00.000', '2009-08-12 08:08:00.000',7 union all
select 1,'2009-08-13 13:06:00.000' ,'2009-08-13 13:08:00.000',2 union all
select 1,'2009-08-12 10:56:00.000', '2009-08-12 11:03:00.000',7

select * from @tbl

Select min(stdt),max(enddt)
from @tbl
where (diff) < 10
union all
Select min(stdt),max(enddt)
from @tbl
where diff >= 10

I should get output as
2009-08-12 08:01:00.000,2009-08-12 08:08:00.000
2009-08-13 13:06:00.000,2009-08-13 13:08:00.000
2009-08-12 10:56:00.000,2009-08-12 11:03:00.000
because the diff between 2009-08-12 08:01:00.000 & 2009-08-12 08:08:00.000 is less that 10.

But by your query I get output
2009-08-12 08:01:00.000,2009-08-13 13:08:00.000

PBUH

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-06 : 08:32:33

Select min(stdt),max(enddt)
from @tbl
where diff < 10

group by stdt
UNION ALL
Select min(stdt),max(enddt)
from @tbl
where diff >= 10
group by stdt

Jim

Everyday I learn something that somebody else already knew

Sachin.Nand

2937 Posts

Posted - 2009-11-06 : 08:48:53

No still not working.

declare @tbl as table(stdt datetime,enddt datetime,diff int)

insert into @tbl

select '2009-08-12 08:08:00.000', '2009-08-12 08:18:00.000' ,10 union all
select'2009-08-12 08:01:00.000', '2009-08-12 08:08:00.000', 7 union all
select '2009-08-12 08:01:00.000', '2009-08-12 08:18:00.000' ,17 union all
select '2009-08-12 08:01:00.000', '2009-08-12 08:04:00.000' ,3 union all
select '2009-08-12 08:04:00.000', '2009-08-12 08:08:00.000', 4 union all
select '2009-08-12 08:04:00.000', '2009-08-12 08:18:00.000',14

select * from @tbl

Select min(stdt),max(enddt)
from @tbl
where diff < 10

group by stdt
UNION ALL
Select min(stdt),max(enddt)
from @tbl
where diff >= 10
group by stdt

PBUH

Sachin.Nand

2937 Posts

Posted - 2009-11-06 : 08:51:01

I know the data is stupid but thats the way it is.I need to find a way out :(

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources