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

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
code-output-desired.doc
mahpogAsked:
Who is Participating?
 
vastoConnect With a Mentor Commented:
try this:
DECLARE @FromDate DATETIME,  @ToDate DATETIME
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, b.room
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:
http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

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.
0
 
vastoCommented:
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:
http://www.r-tag.com/Pages/Sample_OvertimeCalculation.aspx


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

DECLARE @FromDate DATETIME,  @ToDate DATETIME

WITH DatesTable(CurrentDate) AS
    (
        SELECT @FromDate AS datetime
        UNION ALL
        SELECT DATEADD(hh, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate
    )
SELECT CurrentDate FROM DatesTable OPTION (MAXRECURSION 0);
0
 
mahpogAuthor Commented:
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:
CTE-code-results.doc
0
 
mahpogAuthor Commented:
thanks for response
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.