This question is in reference to my last question: SQL Server 08: Union over while
I'm writing a CTE to do what was described in that question (find available time slots over a given number of days.
I have a CTE written, but I can't quite grasp how to get my date incrementing and limited. As it stands, it only gets data from the 2011-07-30 (@ArrivalDate). Can anybody explain to me how I can get it properly incrementing, and where I'm going wrong?
Update: I've come up with a better query for getting all the available time slots
(SELECT TS.UKTNN, TS.TimeSlotID, TS.TimeSlotGroupID
FROM FittingCentreTimeSlotGroupTimeSlots TS)
EXCEPT
(Select BFD.UKTNN, BFD.TimeSlotID, BFD.TimeSlotGroupID
From BookedFittingDates BFD)
And here is the table structure as requested in the comments:
TimeSlotGroup
UKTNN, TimeSlotGroupID, TimeSlotGroupDesc
TimeSlotGroupTimeSlots
UKTNN, TimeSlotID, TimeSlotGroupID, TimeSlotDesc
BookedDates
UKTNN, TimeSlotGroupID, TimeSlotID, Date
Here is what my existing CTE looks like right now
Use[DEV_UKTN_DATA]
GO
DECLARE @UKTNN int;
DECLARE @ArrivalDate date;
DECLARE @NumDays int;
DECLARE @LoopDate date;
SET @UKTNN = 7;
SET @ArrivalDate = '2011-07-30';
SET @LoopDate = @ArrivalDate;
SET @NumDays = 2;
WITH GetTimeSlotsOnDays(TimeSlotGroupID, TimeSlotGroupDesc, TimeSlotID, TimeSlotDescription, AdditionalCost, UKTNN, CurrentDate, Distance)
AS
(
SELECT TSG.TimeSlotGroupID,
TSG.TimeSlotGroupDesc,
TS.TimeSlotID,
TS.TimeSlotDescription,
TS.AdditionalCost,
@UKTNN AS UKTNN,
@ArrivalDate as CurrentDate,
0 As Distance
FROM FittingCentreBranchTimeSlotGroups TSG
INNER JOIN FittingCentreTimeSlotGroupTimeSlots TS
ON TSG.TimeSlotGroupID = TS.TimeSlotGroupID AND
TSG.UKTNN = TS.UKTNN
WHERE TSG.UKTNN = @UKTNN AND
TS.UKTNN = @UKTNN AND
TS.TimeSlotGroupID = TSG.TimeSlotGroupID AND
NOT EXISTS(
SELECT *
FROM BookedFittingDates BFD
WHERE BFD.TimeSlotGroupID = TS.TimeSlotGroupID AND
BFD.TimeSlotID = TS.TimeSlotID AND
BFD.UKTNN = @UKTNN AND
BFD.Date = @ArrivalDate)
--@ArrivalDate <= DATEADD(DAY, @NumDays, @ArrivalDate)
UNION ALL --Recursion starts here
SELECT TSG.TimeSlotGroupID,
TSG.TimeSlotGroupDesc,
TS.TimeSlotID,
TS.TimeSlotDescription,
TS.AdditionalCost,
@UKTNN UKTNN,
@ArrivalDate As CurrentDate,
Distance + 1 As Distance
FROM FittingCentreBranchTimeSlotGroups TSG
INNER JOIN FittingCentreTimeSlotGroupTimeSlots TS
ON TSG.TimeSlotGroupID = TS.TimeSlotGroupID AND
TSG.UKTNN = TS.UKTNN
INNER JOIN GetTimeSlotsOnDays as GTSOD
ON GTSOD.CurrentDate = DATEADD(DAY, Distance, @ArrivalDate)
)
SELECT *
FROM GetTimeSlotsOnDays GTSOD
WHERE GTSOD.CurrentDate = @ArrivalDate AND GTSOD.Distance = 0
ORDER BY GTSOD.TimeSlotGroupID ASC, GTSOD.TimeSlotID ASC
GO
Best Answer
I found an answer for this!
1) Create a function that returns a table containing the desired dates
2) Do a cartesian product of the timeslots with the dates
3) Find the exceptions between the booked dates and the cartesian product