CODEWITHSUNDEEP
sql-server-2008t-sql
WooHoo
WooHoo

Reputation: 1922

Determine available date ranges between a given start and end date

Background (very much simplified): I have banner space that I sell on a website. A banner can be booked for a given start and end date, this banner cannot be double booked. The banner can be booked multiple times in a year but never more than once on the same date.

My problem: When saving a booking, the user enters a start / end date, now a banner may be booked multiple times within this start /end date and I want to automatically book any free dates within the start / end date.

An example:

s|aaaaaaa|s1------e1|aaaaaaaaa|s2------e2|aaaaaa|e

s / e = booking dates entered by user. s1 - e1 = first booking. s2 - e2 = second booking. aaa.. = available date ranges.

In tsql I want to work out the start/end dates for the aaa.. (available dates ranges). Then insert them into the booking table.

The booking table looks like this:

BookingId------BannerId-----StartDate-----ExpiryDate
000000001------00000001-----2012-11-01----2012-11-05
000000002------00000001-----2012-11-10----2012-11-15
000000003------00000001-----2012-11-16----2012-11-20
000000004------00000001-----2012-12-01----2012-12-05

So, if a user enters a start date of 2012-11-04 and an end date of 2012-12-10. The available dates would be.. 2012-11-06 to 2012-11-09, 2012-11-21 to 2012-11-30, 2012-12-06-2012-12-10.

Thanks for any advice on how to do this.

Upvotes: 1

Views: 314

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

This seems to do the trick:

(All one script, but split so that you can see each part) Data setup:

declare @Bookings table (
BookingId char(9) not null,
BannerId char(8) not null,
StartDate date not null,
ExpiryDate date not null
)
insert into @Bookings (BookingId,BannerId,StartDate,ExpiryDate) values
('000000001','00000001','20121101','20121105'),
('000000002','00000001','20121110','20121115'),
('000000003','00000001','20121116','20121120'),
('000000004','00000001','20121201','20121205')

Input:

declare @StartDate date
declare @EndDate date
select @StartDate = '20121104',@EndDate = '20121210'

Query:

;With Ordered as (
    select *,ROW_NUMBER() OVER (PARTITION BY BannerID ORDER BY StartDate) as rn
    from @Bookings
), FreePeriods as (
    select
        o1.BannerId,
        DATEADD(day,1,o1.ExpiryDate) as StartDate,
        DATEADD(day,-1,o2.StartDate) as EndDate
    from
        Ordered o1
            inner join
        Ordered o2
            on
                o1.BannerId = o2.BannerId and
                o1.rn = o2.rn - 1
    where
        DATEDIFF(day,o1.ExpiryDate,o2.StartDate) >= 3
    union all
    select
        BannerId,'00010101',DATEADD(day,-1,MIN(StartDate)) from @Bookings group by BannerID
    union all
    select
        BannerID,DATEADD(day,1,MAX(ExpiryDate)),'99991231' from @Bookings group by BannerID
)
select
    BannerID,
    CASE WHEN @StartDate > StartDate then @StartDate ELSE StartDate END as StartDate,
    CASE WHEN @EndDate < EndDate then @EndDate ELSE EndDate END as EndDate
from
    FreePeriods fp
where
    fp.EndDate >= @StartDate and
    fp.StartDate <= @EndDate

Basically, I organize the booked dates into successive pairs, then use each pair to construct a free period that exists between them. I also fake two more periods - one which runs from the beginning of time until the earliest booking, one which runs from the latest booking to the end of time.

I then, in the final query, find periods which overlap the requested dates, and use some CASE expressions to trim periods that extend beyond the requested dates.

Upvotes: 1

Related Questions