Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

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
0
mahpog
Asked:
mahpog
  • 2
  • 2
1 Solution
 
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
 
vastoCommented:
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
 
mahpogAuthor Commented:
thanks for response
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now