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.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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?

share|improve this answer
    
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 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 at 14:59

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.