Convert date list into date ranges

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)

Convert date list into date ranges

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2008-07-22 : 06:45:31

Hi,

I've created a table of missing dates for various markets.

The table has the columns MarketName and date.

I want to display this as a list of date ranges

Heres some example data

select * into #MissingDate from
(
select 'ALLORD' marketname,cast('26/05/2000' as DATETIME) date union
select 'ALLORD','29/05/2000' union
select 'ALLORD','30/05/2000' union
select 'ALLORD','31/05/2000' union
select 'ALLORD','02/06/2000' union
select 'ALLORD','05/06/2000' union
select 'ALLORD','06/06/2000' union
select 'ALLORD','07/06/2000' union
select 'ALLORD','08/06/2000' union
select 'ALLORD','09/06/2000' union
select 'ALLORD','12/06/2000' union
select 'ALLORD','13/06/2000' union
select 'ALLORD','14/06/2000'
) x

I want to display a range of dates (where the missing days are consecutive) e.g.

Marketname, Fromdate, ToDate
ALLORD, 12/06/2000, 14/06/2000

Can anyone help me
Thanks

Sean

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 06:48:01

group by marketname and take MIN(Date),MAX(Date) for getting dateranges. You want this after each gap?

Sean_B
Posting Yak Master

111 Posts

Posted - 2008-07-22 : 06:52:37

Yes I want to identify all the date ranges for each market, so there will be several date ranges for each market. It would also be useful to ignore weekend dates as well.

Sean

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 06:59:15

quote:
Originally posted by Sean_B

Yes I want to identify all the date ranges for each market, so there will be several date ranges for each market. It would also be useful to ignore weekend dates as well.

Sean

do you have a reference table which can used for comparing to find the gaps?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 07:02:29

Shouldn't the result be

Marketname, Fromdate, ToDate
ALLORD, 10/06/2000, 11/06/2000

for the above posted sample data?
You said you wanted the missing data ranges.

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-22 : 07:07:04

quote:
Originally posted by Peso

Shouldn't the result be
Marketname, Fromdate, ToDate
ALLORD, 10/06/2000, 11/06/2000
for the above posted sample data?
You said you wanted the missing data ranges.

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

No I think he wants the dates which are consecutive.I mean he wants dates 12/06/2000 & 14/06/2000 because they are consecutive dates & maybe he wants 29/05/2000 & 31/05/2000 and also 05/06/2000 & 09/06/2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 07:16:37

Then the result should also be

Marketname, Fromdate, ToDate
ALLORD, 26/05/2000, 31/05/2000

and

Marketname, Fromdate, ToDate
ALLORD, 5/06/2000, 9/06/2000

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

Sean_B
Posting Yak Master

111 Posts

Posted - 2008-07-22 : 07:19:08

Hi,

I want all the date ranges for each market, a range being a consecutive list of dates so there will be several in the example data it would be

ALLORD 26/05/2000 - 26/05/2000
ALLORD,29/05/2000 - 31/05/2000
ALLORD,02/06/2000 - 02/06/2000
ALLORD,05/06/2000 - 09/06/2000
ALLORD,12/06/2000 - 14/06/2000

Although it would be nice to ignore weekend gaps.

There will also be different markets as well.

Sean

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-07-22 : 07:42:00

I think I got what you want.]
Try this

declare @tb table(Id int identity(1,1),MarketName varchar(50),rec1 datetime)
insert @tb
select 'ALLORD', '26/05/2000' union
select 'ALLORD','29/05/2000' union
select 'ALLORD','30/05/2000' union
select 'ALLORD','31/05/2000' union
select 'ALLORD','02/06/2000' union
select 'ALLORD','05/06/2000' union
select 'ALLORD','06/06/2000' union
select 'ALLORD','07/06/2000' union
select 'ALLORD','08/06/2000' union
select 'ALLORD','09/06/2000' union
select 'ALLORD','12/06/2000' union
select 'ALLORD','13/06/2000' union
select 'ALLORD','14/06/2000'

select MarketName ,min(rec1) as mindate,max(rec1) as maxdate
from
(
select t1.MarketName ,t1.rec1,max(t2.rec1) as grp,min(t1.rec1) as grp1
from @tb t1
left outer join @tb t2 on t2.rec1 <= t1.rec1
left outer join @tb t3 on t3.rec1 + 1 = t2.rec1
where t3.rec1 is null
group by t1.rec1,t1.MarketName
) x
group by grp,MarketName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 07:42:56

See this topic with many examples how to do this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422

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

Sean_B
Posting Yak Master

111 Posts

Posted - 2008-07-22 : 07:59:12

Thanks, much appreciated.

Sean

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-22 : 08:00:55

Here are a couple more options to play around with and some old links to even more...

; with
  a as (select isnull(a.marketname, b.marketname) as marketname, isnull(a.date, b.date) as date
        from #MissingDate a full outer join #MissingDate b on a.marketname = b.marketname and a.date = b.date - 1
        where a.date is null or b.date is null)
, b as (select *, row_number() over (partition by marketname order by date) as row from a)
select marketname, min(date) as FromDate, max(date) as ToDate
from b group by marketname, (row + 1) / 2 order by marketname, (row + 1) / 2

; with
  a as (select * from #MissingDate except (select marketname, date+1 from #MissingDate))
, b as (select *, row_number() over (partition by marketname order by date) as row from a)
, c as (select * from #MissingDate except (select marketname, date-1 from #MissingDate))
, d as (select *, row_number() over (partition by marketname order by date) as row from c)
select b.marketname, b.date as FromDate, d.date as ToDate
from b inner join d on b.marketname = d.marketname and b.row = d.row

http://msdn.microsoft.com/en-us/library/aa175780.aspx
http://www.sqlservercentral.com/articles/Advanced+Querying/anefficientsetbasedsolutionforislandsandgaps/1619/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 08:04:41

The fastest of them all (if there is a clustered index over MarketName and Rec1).
http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

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

Sean_B
Posting Yak Master

111 Posts

Posted - 2008-07-22 : 11:12:01

Thanks for all the help, It's solved my immediate problem and given me plenty to think about for a while.

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources