List SQL calendar days with events

Question

I have a table of bookings within my SQL server database, which simply comprises

StartDate (datetime) EndDate (datetime) TypeID (int)

They type ID simply shows what type of booking it is (online, personal etc).

I'd like to be able to create an SQL stored query when that when provided a start date and end date, lists the date in one column and whether it is booked or not in another. To clarify I'd like to include all days within the range, not just the days that it is booked. I'd like to use a '0' for when there is no booking, so it would be either 0 showing it is available, or a type ID to show what type of booking it is on that day.

Ah, also to help with my calendar display on the website, it would be useful to have a third and fourth column which shows whether there is a booking which started on the day, and ended on the day, respectively.

I'd be tremendously grateful for any help.

Thanks,

Nick

score 1 · Answer 1 · answered Jan 03 '14 at 12:58

since i had no idea what your tables look like, i came up with this:

create procedure foo (@startdate date, @enddate date)
as
with bookingtable as (
select CONVERT(date,'20140105',112) as startdate
     , CONVERT(date,'20140115',112) as enddate
     , 'lala' as type
), 
 DateRange AS
(
    SELECT @startdate DateValue
    UNION ALL
    SELECT dateadd(dd,1,DateValue)
    FROM   DateRange
    WHERE  dateadd(dd,1,DateValue) <= @enddate
)
select d.DateValue, b.type 
from DateRange d
left join bookingtable b
on d.DateValue >= b.startdate
and d.DateValue <= b.enddate;

replace the booking table with your booking table and you should be good to go

Thanks, this works too, but other answer included extended functions. — Grenville, Jan 03 '14 at 16:21

score 0 · Accepted Answer · edited May 23 '17 at 12:22

Shooting from the hip here, but this answer will help you get where you need be be:

Get a list of dates between two dates using a function

If you combine the function in that answer with a left join onto your table you should be able to derive the results you require.

Something like this(UNTESTED):

SELECT d.thedate, TypeID
FROM dbo.ExplodeDates('20090401','20090531') as d
LEFT JOIN yourtable as tab
    ON d.thedate BETWEEN StartDate AND EndDate

Of course there are caveats like multiple bookings, groupings etc and there are other methods that can achieve similar results.

Here is another option using CTE.

Query

/*Parameters*/
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '01 Jan 2014'
SET @EndDate = '01 Apr 2014'

/*Test Data*/
DECLARE @bookings AS TABLE(StartDate DATETIME, EndDate  DATETIME, TypeID INT)

INSERT INTO @bookings VALUES ('01 Feb 2014','02 Feb 2014',1)
INSERT INTO @bookings VALUES ('01 Mar 2014','10 Mar 2014',1)

/*Query Body*/
;WITH date_range (calc_date) AS (
    SELECT @StartDate
    UNION ALL 
    SELECT DATEADD(DAY, 1, calc_date)
    FROM date_range
    WHERE DATEADD(DAY, 1, calc_date) < @EndDate)
SELECT calc_date
    ,  TypeID
,  CASE calc_date 
   WHEN StartDate THEN 1 
   ELSE 0 
   END AS IsStartDate
,  CASE calc_date 
   WHEN EndDate THEN 1 
   ELSE 0 
   END AS IsEndDate
FROM date_range dr
LEFT JOIN @bookings b
    ON dr.calc_date BETWEEN StartDate AND EndDate
OPTION (MAXRECURSION 0);

I don't know the number of days your calendar may include so I have added the OPTION (MAXRECURSION 0); to get around the max recursion limit of 100, obviously this is a safeguard so be careful when switching it off.

Results

calc_date               TypeID  IsStartDate IsEndDate
2014-01-21 00:00:00.000 NULL    0           0
2014-01-22 00:00:00.000 NULL    0           0
2014-01-23 00:00:00.000 NULL    0           0
2014-01-24 00:00:00.000 NULL    0           0
2014-01-25 00:00:00.000 NULL    0           0
2014-01-26 00:00:00.000 NULL    0           0
2014-01-27 00:00:00.000 NULL    0           0
2014-01-28 00:00:00.000 NULL    0           0
2014-01-29 00:00:00.000 NULL    0           0
2014-01-30 00:00:00.000 NULL    0           0
2014-01-31 00:00:00.000 NULL    0           0
2014-02-01 00:00:00.000 1       1           0
2014-02-02 00:00:00.000 1       0           1
2014-02-03 00:00:00.000 NULL    0           0
2014-02-04 00:00:00.000 NULL    0           0
2014-02-05 00:00:00.000 NULL    0           0
2014-02-06 00:00:00.000 NULL    0           0
2014-02-07 00:00:00.000 NULL    0           0
2014-02-08 00:00:00.000 NULL    0           0
2014-02-09 00:00:00.000 NULL    0           0
2014-02-10 00:00:00.000 NULL    0           0
2014-02-11 00:00:00.000 NULL    0           0
2014-02-12 00:00:00.000 NULL    0           0
2014-02-13 00:00:00.000 NULL    0           0
2014-02-14 00:00:00.000 NULL    0           0
2014-02-15 00:00:00.000 NULL    0           0
2014-02-16 00:00:00.000 NULL    0           0
2014-02-17 00:00:00.000 NULL    0           0
2014-02-18 00:00:00.000 NULL    0           0
2014-02-19 00:00:00.000 NULL    0           0
2014-02-20 00:00:00.000 NULL    0           0
2014-02-21 00:00:00.000 NULL    0           0
2014-02-22 00:00:00.000 NULL    0           0
2014-02-23 00:00:00.000 NULL    0           0
2014-02-24 00:00:00.000 NULL    0           0
2014-02-25 00:00:00.000 NULL    0           0
2014-02-26 00:00:00.000 NULL    0           0
2014-02-27 00:00:00.000 NULL    0           0
2014-02-28 00:00:00.000 NULL    0           0
2014-03-01 00:00:00.000 1       1           0
2014-03-02 00:00:00.000 1       0           0
2014-03-03 00:00:00.000 1       0           0
2014-03-04 00:00:00.000 1       0           0
2014-03-05 00:00:00.000 1       0           0
2014-03-06 00:00:00.000 1       0           0
2014-03-07 00:00:00.000 1       0           0
2014-03-08 00:00:00.000 1       0           0
2014-03-09 00:00:00.000 1       0           0
2014-03-10 00:00:00.000 1       0           1
2014-03-11 00:00:00.000 NULL    0           0
2014-03-12 00:00:00.000 NULL    0           0
2014-03-13 00:00:00.000 NULL    0           0
2014-03-14 00:00:00.000 NULL    0           0

Great! Works brilliantly thanks, before I mark as answer, have you any idea on how to determine the final part of my question? I.e. the two extra BIT columns stating whether the a booking started, or ended on the day? — Grenville, Jan 03 '14 at 13:17

List SQL calendar days with events

2 Answers2