Hourly scheduled meeting and want to output to show blocked time slots form  5:00 am -9:00 pm

Posted on 2012-08-24
Last Modified: 2012-09-10
I code a sql query to output booked meetings by time but because of data i get

                            room 1         room2    ........
8:00 - 8:10 am          1
8:00 - 820  am                               1

I would like:
                        room 1              room2  ........
8:00 am - 9:00 am  1                      1
9:00 am  -10:00 am  0                    0

I have attached detail
Question by:mahpog
    LVL 18

    Expert Comment

    You should create a temp table with the periods

    Description                                 StartTime   EndTime

    8:00 am - 9:00 am                     8:00                 9:00
    9:00 am  -10:00 am                   9:00                10:00

    and then join this table with your broker data using meeting starttime
    (StartTime   <=MeetingStartTime and MeetingStartTime < EndTime)

    Check this link to see how you can generate the table with the periods on the fly:

    you can use a CTE which is variaton of the function from the list:


    WITH DatesTable(CurrentDate) AS
            SELECT @FromDate AS datetime
            UNION ALL
            SELECT DATEADD(hh, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate

    Author Comment

    okay, here is my attempt at CTE and outputing a specific date of meetings scheduled. Should be a total of 4 meetings ( a count of 4 '1' s but I got all 1's across)

    attaching sytnax and results:
    LVL 18

    Accepted Solution

    try this:
    SELECT @FromDate='1/1/2012',@ToDate ='1/2/2012';
    WITH TimeData (time_started) AS
            SELECT @FromDate AS datetime
            UNION ALL
            SELECT DATEADD(hh, 1, time_started) FROM TimeData WHERE time_started < @ToDate
    SELECT time_started, DATEADD(hh,1,time_started) AS time_ended INTO #Time FROM TimeData OPTION (MAXRECURSION 0);
    SELECT * FROM #Time
    Select a.time_started, a.time_ended,
    FROM #Time a 
      LEFT JOIN rcenter_mdata as b on a.time_started <= b.time_started and b.time_started < a.time_ended
    and b.date_started =  '08/09/2012'

    Open in new window

    Then use this approach to pivot by room:

    You can jump directly to the paragraph staring with "This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses". The code before that is creating the tables.

    Author Closing Comment

    thanks for response

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now