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
