Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
I think you might need to go as far as providing full DDL for the tables involved and sample data inserts. With that, I expect we could come up with a set based query and eliminate the recursion. –  Mark Storey-Smith Aug 4 '11 at 15:21
    
Going to update my post now. Made a (much simpler) query using set logic to get just the available dates. Now I just need to find out how to increment the date properly –  AndyBursh Aug 4 '11 at 15:58
    
By providing DDL and scripts I meant CREATE TABLE statements and data inserts. –  Mark Storey-Smith Aug 4 '11 at 16:17
    
My apologies. I'll spend some time writing those again and post them when I have them! –  AndyBursh Aug 4 '11 at 16:20
add comment

1 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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.