2

I'm attempting to write a query to denormalize some data for integrating to our data warehouse, specifically around generating some time slots.

In my source system, I have table that has a start time and and end time and then a column that designates how many appointment slots are available per 15 minute interval.

Example:

    Location | Start Time | End Time | Appointment Slots
    A        | 9:00 am    | 5:00 pm  | 2
    B        | 9:00 am    | 5:00 pm  | 1

I have already been able to generate a list of 15 minute blocks using a CTE. However, each 15 minute slot just says that 2 slots are available for location A and 1 slot is available for location B. What I'd like is to be able to generate 2 rows per 15 minutes for location A and 1 row per 15 minutes for location B.

4

This is an example of when a "Number" table becomes very handy! If you are not familiar with this concept, read this blog post from Adam Machanic: You REQUIRE a Numbers Table

The numbers table is used to join to the Appointment Slots column (in this case) to duplicate each row AppointmentSlots number of times.

-- A simple recreation of your CTE data...
CREATE  TABLE #AppointmentPeriods
(
    AppointmentLocation CHAR(1),
    AppointmentStartTime TIME,
    AppointmentEndTime TIME,
    AppointmentSlotsAvailable SMALLINT
)

INSERT  INTO #AppointmentPeriods VALUES
    ('A', '09:00', '09:15', 2),
    ('A', '09:15', '09:30', 2),
    ('A', '09:30', '09:45', 2),
    -- ....
    ('A', '16:30', '16:45', 2),
    ('A', '16:45', '17:00', 2),
    ('B', '09:00', '09:15', 1),
    ('B', '09:15', '09:30', 1),
    ('B', '09:30', '09:45', 1),
    -- ....
    ('B', '16:30', '16:45', 1),
    ('B', '16:45', '17:00', 1)


-- Numbers table
CREATE  TABLE #Numbers (NumberValue SMALLINT)

INSERT  INTO #Numbers
-- TOP value should be changed so it is greater than the
-- maximum number of potential appointment slots any location can have
SELECT  TOP 20
        ROW_NUMBER() OVER (ORDER BY object_id)
FROM    sys.objects


SELECT  #AppointmentPeriods.*, NumberValue AS AppointmentSlotNumber
FROM    #AppointmentPeriods
        INNER JOIN #Numbers
            ON AppointmentSlotsAvailable >= NumberValue

Query output:

Location | StartTime | EndTime | AppointmentSlots | SlotNumber
A        | 09:00     | 09:15   | 2                | 1
A        | 09:00     | 09:15   | 2                | 2
A        | 09:15     | 09:30   | 2                | 1
A        | 09:15     | 09:30   | 2                | 2
B        | 16:30     | 16:45   | 1                | 1
B        | 16:45     | 17:00   | 1                | 1

PS: There are many ways to generate a numbers table: SO: What is the best way to create and populate a numbers table?

  • Awesome! I knew there would be a handy way to do this. I'll be testing this out tomorrow and will let you know how it works. – njkroes Feb 21 '14 at 2:21
  • Works like a charm! I actually modified your approach by creating using a CTE to create a numbers table on the fly for the max appointment slots available and used that. Thanks! – njkroes Feb 21 '14 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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