SQLTeam.com | Weblogs | Forums

Best way to record booking / availability dates?


#1

Evening all,

I'm looking for the best / most efficient way to record booking/availability dates in a SQL 2008 database. Booking/Availability can be a single day, consecutive days (not including weekends) or non consecutive days. At the moment I'm recording every date the booking contains in seperate rows. I have to wonder if this is the most efficient approach or is there a better way to achieve this...?

Many thanks

Dave

Hopefully this will give you an idea of how its layed out....

CREATE TABLE Availability
(
A_ID Int NOT NULL Primary Key,
A_BookingID Int,
A_SupplierID Int,
A_BookingDate Date,
A_BookingCode VarChar(15)
)

INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1001', '2001', '3001', '2015-11-18', 'Booked')

INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1002', '2002', '3002', '2015-11-18', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1003', '2002', '3002', '2015-11-19', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1004', '2002', '3002', '2015-11-20', 'Booked')

INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1005', '2003', '3003', '2015-11-18', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1006', '2003', '3003', '2015-11-20', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1007', '2003', '3003', '2015-11-23', 'Booked')

#2

Without knowing anything about your business requirements or logic, it would be difficult to give any useful suggestions on how you should layout the tables. You can have fewer rows possibly without sacrificing anything in terms of simplicity by having a startdate column and an enddate column to record a single booking that spans over a date range as a single row in the table.


#3

Hi James,

Thanks for your reply. I understand and apreciate where your coming from, at the moment I can't help but think I'm missing a trick. I did wonder if there are any special data types that might help me out... The other thing that crosses my mind, is there a way in SQL of taking 2 dates ie a start date of 2015-11-18 and an end date of 2015-11-24 and creating records for each weekday included in them...

Thanks

Dave


#4

If you have a calendar table or a numbers table in your database, generating the sequence of dates is easy. If you don't have one, you can create one temporarily like this:

CREATE TABLE #Calendar
	(Date DATE NOT NULL PRIMARY KEY);
	
;WITH cte(N) AS
(
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
INSERT INTO #Calendar
( Date )
SELECT DATEADD(dd, ROW_NUMBER() OVER (ORDER BY a.N) , '20151118')
FROM cte a CROSS JOIN cte b CROSS JOIN cte  c

Very often people add additional columns to the calendar table to indicate whether it is a holiday, or weekend, or some other type of special date.

Once you have a calendar date, generating the set of dates can be done like this:
SELECT
Date
FROM
#Calendar c
WHERE
Date >= '20151118'
AND Date <= '20151124'


#5

James, the calendar begins at 20151119 so should it be ROW_NUMBER() OVER (ORDER BY a.N) - 1?


#6

Yes of course :smile: thank you.


#7

Hiya,

Oooooooh, oooh!! I like this...! I'm not going to pretend I understand it all but essentially it works...

I get that I need to start the date generation 1 day earlier but I'm not sure how to put dj55's - 1 in there. It doesn't like

SELECT DATEADD(dd, ROW_NUMBER() OVER (ORDER BY a.N) , '20151118' -1)

The other one I don't understand is why it only creates 125 rows, what is it that sets this limit?

Thanks

Dave


#8

Subtract 1 from the second term within the DATEADD like shown below:

SELECT DATEADD(dd, ROW_NUMBER() OVER (ORDER BY a.N)-1 , '20151118')

If you look at each part of the query that fills the #Calendar table, you can discern why it is inserting 125 rows. First, the inner query within the cte which is:

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

If you run that, you will see that it generates 5 rows. Then, you are doing a cross join on that table to itself 3 times. Cross join between two tables produce M x N rows if one table has M rows and the other N rows. So in this case, it produces 5x5x5 = 125. If you wanted 5 times that, just cross join it one more time.

cte a CROSS JOIN cte b CROSS JOIN cte c CROSS JOIN cte d

Add one more UNION all to the inner query and you will be getting 6x6x6 rows instead of 5x5. You can remove the insert part of the query which is
INSERT INTO #Calendar ( Date )
and run it with various combinations to see what it produces.
` ;WITH cte(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)

SELECT 
	DATEADD(dd, ROW_NUMBER() OVER (ORDER BY a.N) , '20151118'),
	ROW_NUMBER() OVER (ORDER BY a.N)-1
FROM cte a CROSS JOIN cte b CROSS JOIN cte  c`

#9

Hi James,

Fantastic explanation, thank you. I can now see how it works, I don't quite understand it 100% yet (I'm still reading up on CTE) but I'm getting there. I'm pretty sure this is the way I'm going to go in reference to storing the availability / bookings...

Once again, thank you for you help & explanation!

Dave