Looping through dates with sets instead of a while loop in TSQL

Question

I have a stored procedure that goes through a date range, and for each date, looks for a bookable object that is not booked at that date. I am struggling to find a way to convert from this functioning stored procedure that uses while-loop and a temporary table into using something more efficient:

declare @from datetime
declare @to datetime
declare @currentdate datetime
declare @hotelid int

set @from = '2018-06-14 17:00'
set @to = '2019-06-25 14:00'
set @currentdate = DATEADD(dd, DATEDIFF(dd, 0, @from), 0)
set @hotelid = 2

Create table dbo.#AvailableObjectsDateRange
(
Date datetime not null,
Name nvarchar(max) not null,
ObjectId bigint not null,
ProductId bigint not null,
ParentProductId bigint null
)

WHILE (@currentdate < DATEADD(dd, DATEDIFF(dd, 0, @to), 0))
BEGIN
;with Reserved as (select bookableobjectid from hotell.BookingRows as br
join hotell.bookings as b on br.bookingid = b.Id
where b.HotelId = @hotelid and br.bookedto > DateAdd(dd, 1, @currentdate) and 
br.bookedfrom < DateAdd(dd, 1, @currentdate) and br.checkedout = 0
and (br.isactive = 1  and b.IsActive = 1))

insert into #AvailableObjectsDateRange select @currentdate, bo.Name, bo.Id, 
p.Id, p.ParentId from hotell.BookableObjects as bo 
join hotell.products as p on bo.ProductId = p.Id
where bo.hotelid = @hotelid
and UnactivatedAt is null
and p.isaddon = 0
and bo.isactive = 1
and bo.id not in (select * from reserved)

SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); /*increment current date*/

END
select * from #AvailableObjectsDateRange order by Date
drop table #AvailableObjectsDateRange

It's performance is alright for our cases, but it could probably be much improved still.

Could you walk us through the code, or better: what is functionally happening here? You're inserting records and excluding some other records that are active? — NickyvV
– NickyvV, Commented Jun 14, 2018 at 9:37
you can basically ignore the create table insert into stuff since thats just a temp table so that the stored proc returns just one result set instead of one per day. i am selecting the objects that are not in the CTE Reserved, which on the other hands returns the ids of the reserved objects. it selects the bookingrows that has bookedto after date and bookedfrom before date, and then does some checking that the booking is still active. — eatfrog
– eatfrog, Commented Jun 14, 2018 at 11:14

Hugo Kornelis · Accepted Answer · 2018-06-14 11:37:52Z

2

It looks like you are populating a table with one row for each day within the range and for each "object" (hotel? room?) that is not already reserved for that day. Correct?

Best and fastest way to do this is fully set based. For this you will need a calendar table (google it) and a table of all objects. The query itself then simply is a cross join between those two tables, with a NOT EXISTS to exclude reserved objects. Something like (untested pseudocode):

INSERT INTO dbo.#AvailableObjectsDateRange (column list)
SELECT column list
FROM dbo.Calendar AS c
CROSS JOIN dbo.ObjectList AS o
WHERE c.Date >= @from
AND c.Date < @to
AND NOT EXISTS
   (SELECT *
    FROM bookings AS b
    WHERE c.Date >= b.bookingfrom
    AND c.Date < b.bookingto
    AND c.ObjectID = o.ObjectID);

You'll probably need to tweak the date logic a bit and add in the specific extra things from your code.

EDIT: In response to your comment: you'll have to replace the subquery with your actual logic to get all the bookings. Similar to what you have in the CTE in your question.I didn't want to repeat that in the answer because I wanted to focus on the logical structure: cross join dates (in range) with objects, then exclude those that are reserved

edited Jun 14, 2018 at 11:37

answered Jun 14, 2018 at 10:45

Hugo Kornelis

2512 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eatfrog Over a year ago

its a temp table, otherwise i would get one result set per day, then i return that entire temp table. yeah, i have a calendar table already. i understand your pseudo-code, but its not really close enough for me to be able to tweak it to work.. there are three tables involved here, bookableobjects, bookings and bookingrows.

Collectives™ on Stack Overflow

Looping through dates with sets instead of a while loop in TSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related