Help with grouping non-contiguous ranges

Question

Post reply

Help with grouping non-contiguous ranges

Margaret Dullnig

Old Hand

Points: 373
More actions
February 15, 2008 at 2:58 pm

#75852

I have a parent table where each record is a date for a person and division. For the most part, the dates are contiguous. There is a child table that should have a record corresponding to each parent record. I need to find the dates where a child record does not exist. However, I have to present it in ranges for each person/division. For example, if John had these child records missing:
Div1 07/11, 07/12,07/13, 08/05
Div2 12/11,12/12
Then the output should look like:
John Div1 07/11 - 0713
John Div1 08/05 - 08/05
John Div2 12/11 - 12/12
I wrote script that use table data types to loop through and test each value, but it runs forever.
Any suggestions on using min/max or some other way?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

See if this helps

create table missingdates(person varchar(10),division varchar(10), dt datetime)

insert into missingdates(person,division,dt)

select 'John','Div1','20080711' union all

select 'John','Div1','20080712' union all

select 'John','Div1','20080713' union all

select 'John','Div1','20080805' union all

select 'John','Div2','20081211' union all

select 'John','Div2','20081212'

select lbound.person,

lbound.division,

lbound.dt as Start,

min(ubound.dt) as Stop

from (

select t1.person,t1.division,t1.dt

from missingdates t1

where not exists(select *

from missingdates t2

where t1.dt=t2.dt+1

and t1.person=t2.person

and t1.division=t2.division)) lbound

inner join

(select t1.person,t1.division,t1.dt

from missingdates t1

where not exists(select *

from missingdates t2

where t1.dt=t2.dt-1

and t1.person=t2.person

and t1.division=t2.division)) ubound

on lbound.person=ubound.person

and lbound.division=ubound.division

and lbound.dt<=ubound.dt

group by lbound.person,lbound.division,lbound.dt

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Ian Yates SSCoach Points: 19738 More actions · Answer 2

The google-powered search on this site works wonders... 🙂

Search for dates gaps.

See these links

http://www.sqlservercentral.com/Forums/Topic145928-197-1.aspx#bm437872

http://www.sqlservercentral.com/Forums/Topic452000-356-1.aspx

Finding gaps and reporting ranges is a commonly discussed issue with many solutions 🙂

Margaret Dullnig Old Hand Points: 373 More actions · Answer 3

Thanks, Mark. This looks like what I attempted but couldn't quite get. I will try it first thing Monday morning.

Margaret Dullnig Old Hand Points: 373 More actions · Answer 4

Thank you for your reply. I actually did use the google search for date ranges, but didn't really hit on anything that helped. I use this site a lot and have retrieved good advice and examples via the search probably 95% of the time. This time it just didn't work for me. This may be the second time I posted to this site in over three years. I am truly sorry if my post was an intrusion.