Checking a range of dates in SQL

问题:

Following on from a question I put on yesterday, I need to return a range of "available" dates for a laptop rollout "booking system". I want to populate a table of possible available dates a user can book a slot on by checking for each date the total possible number of slots, and subtracting the number of slots already booked.

The logic is as follows:

• A technician can build 3 laptops per day.
• On any day there may be 1, 2 or 3 technicians available.
• A table holds the bookings made
• I don't want a table of all possible dates, I want to calculate it on the fly

Relevant tables are:

tl_sb_slotBooking This contains the bookings already made

tl_sb_availabilityPeriods This is used to calculate the total number of available slots on a given day

I can bring back a list of dates with a fixed maximum number (in this case 3) of slots:

    DECLARE @startDate DATE
    DECLARE @endDate DATE

    SET @startDate = GETDATE()
    SET @endDate = DATEADD(m,3,@startDate)
    ;
    WITH dates(Date) AS 
    (
       SELECT @startdate as Date
       UNION ALL
       SELECT DATEADD(d,1,[Date])
         FROM dates 
         WHERE DATE < @enddate
    )

    SELECT Date
      FROM dates 
    EXCEPT
    SELECT date
    FROM tl_sb_booking
    GROUP BY date
    HAVING COUNT(date) >= 3

However, the maximum won't always be 3, it changes for each day.

I can find the maximum possible slots for a given day:

    DECLARE @myDate DATETIME = '2013-06-22'

    SELECT SUM(laptopsPerDay) AS totalSlots
       FROM tl_sb_technicianAvailability
       WHERE startDate <= @myDate AND endDate >= @myDate
       AND availabiltyStateID=3

it will bring back 6 as the total number of slots available for 2013-06-22. (The availabilityStateID field is used to store available/unavailable etc.)

So, the bit I am stuck on is combining the two.

What I want is for each possible date, if the number of slots already booked is less than the number of possible slots for that day, add it to the table being returned (otherwise don't).

回答1:

Firstly, althought you are only generating a small list, using a CTE to generate a sequential list performs terribly and is best avoided.

For the sake of this I will use the system table Master..spt_values for a sequential list of numbers, but if you are worried about using undocumented system tables then there are other methods in the link above.

The first thing I would do is split the technician availability dates into a row per day, this will allow for technicians who are available for only part of the peiod required (e.g. from your sceen shot of the table if you wanted to query from 18th June to 26th June none of the technicians show would appear as available using the query you have posted):

SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
        ta.TechnicianID,
        ta.LapTopsPerDay
FROM    tl_sb_technicianAvailability ta
        INNER JOIN Master..spt_values spt
            ON spt.Type = 'P'
            AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)

This would simply turn:

TechnicianID    StartDate   EndDate     LapTopsPerDay
1               20130620    20130624    3

into

Date        TechnicianID    LapTopsPerDay
20130620    1               3
20130621    1               3
20130622    1               3
20130623    1               3
20130624    1               3

You can then limit this list to the date range required, and sum up the total laptops than can be done as this is not needed on a technicial level:

WITH ExplodedAvailability AS
(       SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
                ta.TechnicianID,
                ta.LapTopsPerDay
        FROM    tl_sb_technicianAvailability ta
                INNER JOIN Master..spt_values spt
                    ON spt.Type = 'P'
                    AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)
)
SELECT  Date, TotalLaptops = SUM(LapTopsPerDay)
FROM    ExplodedAvailability
WHERE   Date >= @StartDate
AND     Date < @EndDate
GROUP BY Date;

Finally you can LEFT JOIN to the bookings table to get the available slots per day

WITH ExplodedAvailability AS
(       SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
                ta.TechnicianID,
                ta.LapTopsPerDay
        FROM    tl_sb_technicianAvailability ta
                INNER JOIN Master..spt_values spt
                    ON spt.Type = 'P'
                    AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)
), Availability AS
(   SELECT  Date, TotalLaptops = SUM(LapTopsPerDay)
    FROM    ExplodedAvailability
    WHERE   Date >= @StartDate
    AND     Date < @EndDate
    GROUP BY Date
), Bookings AS
(   SELECT  Date, SlotsBooked = COUNT(*)
    FROM    tl_sb_booking
    GROUP BY Date
)
SELECT  Availability.Date,
        Availability.TotalLaptops,
        RemainingSlots = Availability.TotalLaptops - ISNULL(Bookings.SlotsBooked, 0)
FROM    Availability
        LEFT JOIN Bookings
            ON Bookings.Date = Availability.Date;

I think what you are after is to add a booking to the next available day, so the query to do this would be:

DECLARE @UserID INT = 1;

WITH ExplodedAvailability AS
(       SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
                ta.TechnicianID,
                ta.LapTopsPerDay
        FROM    tl_sb_technicianAvailability ta
                INNER JOIN Master..spt_values spt
                    ON spt.Type = 'P'
                    AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)
), Availability AS
(   SELECT  Date, TotalLaptops = SUM(LapTopsPerDay)
    FROM    ExplodedAvailability
    WHERE   Date >= CAST(GETDATE() AS DATE)
    GROUP BY Date
), Bookings AS
(   SELECT  Date, SlotsBooked = COUNT(*)
    FROM    tl_sb_booking
    GROUP BY Date
)
INSERT tl_sb_slotBooking (UserID, Date)
SELECT  @UserID, MIN(Availability.Date)
FROM    Availability
        LEFT JOIN Bookings
            ON Bookings.Date = Availability.Date
WHERE   Availability.TotalLaptops > ISNULL(Bookings.SlotsBooked, 0)

回答2:

Should this be of use to anyone, this is the way I ultimately did it:

DECLARE @startDate DATE
DECLARE @endDate DATE

SET @startDate = GETDATE()
SET @endDate = DATEADD(m,3,@startDate)
;
WITH dates(currentDate) AS 
(
   SELECT @startdate as currentDate
   UNION ALL
   SELECT DATEADD(d,1,[currentDate])
     FROM dates 
     WHERE currentDate < @enddate
)

SELECT currentDate
  FROM dates
     WHERE              /* slots booked for date */
                       (      
                              SELECT count([date])
                              FROM tl_sb_booking
                              where [date] = currentDate
                       ) 

                       <

                       /* total slots available */
                       (
                              SELECT SUM(laptopsPerDay) AS totalSlots
                              FROM tl_sb_technicianAvailability
                              WHERE startDate <= currentDate AND endDate >= currentDate
                              AND availabiltyStateID=3
                       )