Identifing Gaps in Date Ranges - SQL Server 2005

liquidlogic
liquidlogic used Ask the Experts™
on
Hello there,

I've got the following problem and I'm struggling to come up with a solution that works in all circumstances.

I have 2 tables ActivePeriod and ActiveCategory.

For each date range entered into ActivePeriod, there must be a entry (or entries) in ActiveCategory to cover the date range specified in ActivePeriod. i.e. there can be no gaps.
There can also be multiple entires in ActivePeriod for each client, but these entires will not have date overlaps.

I'm trying to identify any gaps in ActiveCategory for each entry in ActivePeriod

I've tried a few different approaches, but I can't seem to find a working solution.

Here is some SQL to create the two tables and a couple of sample records

CREATE TABLE ActivePeriod (PeriodID int identity, PersonID int, StartDate datetime, enddate datetime)
go
INSERT INTO ActivePeriod ([PersonID], [StartDate], [EndDate]) VALUES (1, 'Jan 01 2008', 'Jan 01 2010')
INSERT INTO ActivePeriod ([PersonID], [StartDate], [EndDate]) VALUES (2, 'Feb 01 2008', 'Feb 01 2009')
INSERT INTO ActivePeriod ([PersonID], [StartDate], [EndDate]) VALUES (2, 'Sep 01 2009', null)
go

CREATE TABLE ActiveCategory (CategoryID int identity, PersonID int, StartDate datetime, enddate datetime)
go
INSERT INTO ActiveCategory ([PersonID], [StartDate], [EndDate]) VALUES (1, 'Jan 01 2008', 'Jan 01 2009')
INSERT INTO ActiveCategory ([PersonID], [StartDate], [EndDate]) VALUES (1, 'Feb 01 2009', 'May 01 2009')
INSERT INTO ActiveCategory ([PersonID], [StartDate], [EndDate]) VALUES (1, 'Jul 01 2009', 'Jan 01 2010')
INSERT INTO ActiveCategory ([PersonID], [StartDate], [EndDate]) VALUES (2, 'Mar 01 2008', 'Feb 01 2009')
INSERT INTO ActiveCategory ([PersonID], [StartDate], [EndDate]) VALUES (2, 'Dec 01 2009', null)
go

--drop table ActivePeriod
--drop table ActiveCategory

Open in new window


So basically, for PersonID 1 it looks like this

   ActivePeriod.StartDate                           activePeriod.EndDate
        FR|--------------------|TO                                   |               
             |                                                                    |
             |                  GAP|-----|GAP                         |
             |                                                                    |
             |                             FR|----------|TO             |               
             |                                                                    | 
             |                                          GAP|-------------|GAP  
             |                                                                    | 
             |                                                                    |                    

I'm interested in returning the date ranges contained in the it the two GAP periods shown above

So something along the lines of

PersonID            ActivePeriodStartDate            ActivePeriodEndDate            GapFrom                  GapTo
1                          Jan 01 2008                                Jan 01 2010                             Jan 02 2009            Jan 31 2009
1                          Jan 01 2008                                 Jan 01 2010                            May 02 2009            Jan 01 2010
2                          Feb 01 2008                                Feb 01 2009                             Feb 01 2008            Feb 28 2008
2                          Sep 01 2009                                null                                            Sep 01 2009             Nov 30 2009

Does anyone have any ideas?

Thanks for taking the time to look at this.

Paul
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Something like this
select p.PersonID, p.StartDate, p.endDate,   isnull(c.endDate,p.StartDate), isnull(min(c1.StartDate)-1,p.endDate)
      from ActivePeriod p
            join ActiveCategory c on p.PersonID =c.personID and p.StartDate<=c.StartDate and isnull(p.enddate,getdate())>=isnull(c.enddate,getdate())
            left join ActiveCategory c1 on c.PersonID =c1.personID and c1.StartDate>=isnull(c.enddate,GETDATE()) and c1.endDate
            group by p.PersonID, p.StartDate, p.endDate,   c.endDate
            order by p.PersonID, p.StartDate, c.endDate

Author

Commented:
Sorry, I;'ve just realised my expected result set had an error in it - it should look like this

PersonID            ActivePeriodStartDate            ActivePeriodEndDate            GapFrom                  GapTo
1                          Jan 01 2008                                Jan 01 2010                             Jan 02 2009            Jan 31 2009
1                          Jan 01 2008                                 Jan 01 2010                            May 02 2009            Jul 30 2010
2                          Feb 01 2008                                Feb 01 2009                             Feb 01 2008            Feb 28 2008
2                          Sep 01 2009                                null                                            Sep 01 2009             Nov 30 2009

Author

Commented:
Thanks for feedback trofimoval,

One problem is that your last and clause is incomplete - what did you intend to have there?

left join ActiveCategory c1 on c.PersonID =c1.personID and c1.StartDate>=isnull(c.enddate,GETDATE()) [b]and c1.endDate[/b]
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.


Not sure what rule you apply to come up with this gap in your sample output
 May 02 2009            Jul 30 2010

This query will get gep as
2009-05-02 - 2009-06-30
since category row defined as
INSERT INTO ActiveCategory ([PersonID], [StartDate], [EndDate]) VALUES (1, 'Jul 01 2009', 'Jan 01 2010')

-- get empty from period start
select p.PersonID, p.StartDate, p.endDate,  p.StartDate  gapFrom, min(c.StartDate-1) gapTo
--      select p.PersonID, p.StartDate, p.endDate,  p.StartDate  gapFrom, c.StartDate-1 gapTo
      from ActivePeriod p
              join ActiveCategory c on p.PersonID =c.personID
                        and p.StartDate<c.StartDate and isnull(p.enddate,getdate())>=isnull(c.enddate,getdate())
      where not exists (select 1 from ActiveCategory where  PersonID =p.personID  and StartDate= p.StartDate )
group by p.PersonID, p.StartDate, p.endDate,  p.StartDate
union
-- period end gaps
select p.PersonID, p.StartDate, p.endDate,  max(c.enddate+1)  gapFrom, p.endDate gapTo
--      select p.PersonID, p.StartDate, p.endDate,  c.enddate+1  gapFrom, p.endDate gapTo
      from ActivePeriod p
              join ActiveCategory c on p.PersonID =c.personID
                        and p.StartDate<c.StartDate and isnull(p.enddate,GETDATE())>isnull(c.enddate,GETDATE())
            where not exists (select 1 from ActiveCategory where  PersonID =p.personID  and endDate= p.endDate )
group by p.PersonID, p.StartDate, p.endDate
union
-- mid gaps
select p.PersonID, p.StartDate,p.enddate , c1.enddate+1  gapFrom, min(c2.StartDate-1) gapTo
--select p.PersonID, p.StartDate,p.enddate , c1.enddate+1  gapFrom, min(c2.StartDate-1) gapTo
      from ActivePeriod p
              join ActiveCategory c1 on p.PersonID =c1.personID
                  and p.StartDate<=c1.StartDate and ISNULL(p.enddate,GETDATE()) >=ISNULL(c1.enddate,GETDATE())
              join ActiveCategory c2 on p.PersonID =c2.personID  
                  and p.StartDate<=c2.StartDate and ISNULL(p.enddate,GETDATE()) >=ISNULL(c2.enddate,GETDATE())  
                        and isnull(c1.enddate,p.enddate)<=c2.StartDate
group by p.PersonID, p.StartDate,p.enddate,  c1.enddate+1

Author

Commented:
Thanks again for that trofimoval,

There are a couple of errors in my original post, but as I can't edit the post there is little I can do about it.

I'll work through your solution and let you know how I get on.

Paul
I've eventually found a solution that works in all circumstances.

Basically I'm joining ActiveCategory to itself with a full join to create a CTE of any date gaps, then joining the CTE to the ActivePeriod table to see which gaps intersect with which periods.

Thanks for all the help

Paul

Author

Commented:
My solution works

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial