• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

SQL Server query to create calendar grid

Screenshot of resultHello.  I've been working on a SQL query that can show me in a grid format the schedules of presenters.  The query pulls from a single SQL table:

select presenter,
            case
            when 0 between datepart(hour,datetime)+ convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '12AM',
            case
            when 0.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '12:30AM',
            case
            when 1.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '1AM',
            case
            when 1.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '1:30AM',
            case
            when 2.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '2AM',
            case
            when 2.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '2:30AM',
            case
            when 3.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '3AM',
            case
            when 3.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '3:30AM',
            case
            when 4.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '4AM',
            case
            when 4.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '4:30AM',
            case
            when 5.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '5AM',
            case
            when 5.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '5:30AM',
            case
            when 6.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '6AM',
            case
            when 6.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '6:30AM',
            case
            when 7.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '7AM',
            case
            when 7.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '7:30AM',
            case
            when 8.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '8AM',
            case
            when 8.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '8:30AM',
            case
            when 9.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '9AM',
            case
            when 9.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '9:30AM',
            case
            when 10.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '10AM',
            case
            when 10.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '10:30AM',
            case
            when 11.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '11AM',
            case
            when 11.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '11:30AM',
            case
            when 12.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '12PM',
            case
            when 12.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '12:30PM',
            case
            when 13.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '1PM',
            case
            when 13.51 between datepart(hour,datetime)+ convert(decimal(9,2),datepart(minute,enddatetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '1:30PM',
            case
            when 14.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '2PM',
            case
            when 14.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '2:30PM',
            case
            when 15.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '3PM',
            case
            when 15.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '3:30PM',
            case
            when 16.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '4PM',
            case
            when 16.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '4:30PM',
            case
            when 17.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '5PM',
            case
            when 17.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '5:30PM',
            case
            when 18.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '6PM',
            case
            when 18.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '6:30PM',
            case
            when 19.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '7PM',
            case
            when 19.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '7:30PM',
            case
            when 20.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '8PM',
            case
            when 20.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '8:30PM',
            case
            when 21.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '9PM',
            case
            when 21.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '9:30PM',
            case
            when 22.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '10PM',
            case
            when 22.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '10:30PM',
            case
            when 23.01 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '11PM',
            case
            when 23.51 between datepart(hour,datetime)+convert(decimal(9,2),datepart(minute,datetime)/60.00) and datepart(hour,enddatetime)+convert(decimal(9,2),datepart(minute,enddatetime)/60.00) then 'Scheduled'
            else 'X'
            end as '11:30PM'
            from workshops$
            where datetime between '2012-08-24 00:00:00.000' and '2012-08-24 00:00:00.000'

When I execute the query I get the following result (see attached)

I am getting the results in the way I'd hope with one exception--in the case where a presenter has multiple workshops in a single day, a row is being returned for each one of them.  What I want to see is a single row for each presenter, and if the presenter has multiple appointments in the day, I'd like those time slots to show as 'scheduled' in a single row.  Given the query above, can it be modified to show a single row for each presenter?  I am open to suggestions!

If it helps, I have full access to the database.  It is a SQL Server 2000 instance.  If more information is needed to help answer this question, I can provide, but in short it's a simple table with a presenter name, date/time of workshop, etc.  We have presenters that conduct multiple workshops per day at times.  

Thanks.
0
FPLNET
Asked:
FPLNET
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
Given the query above, can it be modified to show a single row for each presenter?  I am open to suggestions!
Please post the schema to your table (CREATE TABLE) and some sample data.
0
 
FPLNETAuthor Commented:
Below is the table--the data in the spreadsheet is minimal, including only the information that would be relevant.  Thanks!


CREATE TABLE [dbo].[Workshops$](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [DATETIME] [datetime] NOT NULL,
      [CLIENT] [nvarchar](255) NULL,
      [STATE] [nvarchar](255) NULL,
      [CITY] [nvarchar](255) NULL,
      [COUNTY] [nvarchar](255) NULL,
      [STREETADDRESS] [nvarchar](255) NULL,
      [ROOM] [nvarchar](50) NULL,
      [TYPE] [nvarchar](255) NULL,
      [PRESENTATIONMETHOD] [nvarchar](255) NULL,
      [DURATION] [nvarchar](50) NULL,
      [PRESENTER] [nvarchar](255) NOT NULL,
      [PRESENTERNOTIFIEDFLAG] [nvarchar](5) NULL,
      [EYCONTACT] [nvarchar](255) NULL,
      [PHONE] [nvarchar](53) NULL,
      [CLIENTCONTACT] [nvarchar](255) NULL,
      [CLIENTPHONE] [nvarchar](50) NULL,
      [CLIENTFAX] [nvarchar](50) NULL,
      [CLIENTEMAIL] [nvarchar](50) NULL,
      [MATERIALSSHIPPINGINFO] [nvarchar](50) NULL,
      [FPLEEENROLLMENT] [nvarchar](50) NULL,
      [NOTES] [nvarchar](255) NULL,
      [CREATEDATE] [datetime] NULL,
      [DATAFINAL] [nvarchar](50) NULL,
      [ZIPCODE] [nchar](10) NULL,
      [ENDDATETIME]  AS ([datetime] + convert(float,[duration]) / 24),
 CONSTRAINT [PK_Workshops$] PRIMARY KEY CLUSTERED
(
      [DATETIME] ASC,
      [PRESENTER] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SampleEEdata.xls
0
 
Anthony PerkinsCommented:
Unfortunately, since you are using still using SQL Server 2000 it is quite a challenge to come up with a good solution.  There are two reasons for that:
1.  There is no time data type.  This can easily be solved by using a smallint to describe times.
2.  The biggest problem is the lack of PIVOT in SQL Server 2000.

Having said that, here is what you need to do.
1.  Create a table called PresentationTimes as follows:
CREATE TABLE PresentationTimes(
	PresentationTime smallint NOT NULL,
 CONSTRAINT PK_PresentationTimes PRIMARY KEY CLUSTERED 
) 

Open in new window

2. Populate it as follows:
DECLARE @PresentationTime smallint

SET @PresentationTime = 0

WHILE @PresentationTime < 1440
	BEGIN
		INSERT dbo.PresentationTimes(PresentationTime)
		VALUES  (@PresentationTime)
		
		SET @PresentationTime = @PresentationTime + 30
	END

Open in new window

3. Create a VIEW like this:
CREATE VIEW [dbo].[vw_PresentationTimes]

AS

SELECT  w.Presenter,
        CONVERT(varchar(5), DATEADD(minute, t.PresentationTime, 0), 108) PresentationTime
FROM    dbo.PresentationTimes t
        LEFT JOIN dbo.Workshops$ w ON t.PresentationTime = DATEPART(hour, w.[DATETIME]) * 60 + DATEPART(minute, w.[DATETIME])
WHERE   t.PresentationTime BETWEEN 480 AND 1020 -- Change the times as appropriate

Open in new window


If you execute this VIEW you will see the values in an unpivoted format.

4. You now have to find some way to PIVOT the results.  For example, there is the script here http://www.itrain.de/knowhow/sql/tsql/pivot/sp_transform_v1_1.asp

Unfortuantely, this scripts as well as many others expects an aggregate function, which does not apply in your case.
0
 
Anthony PerkinsCommented:
Here is a complete solution, somewhat based on the previous comments and using Dynamic SQL.
1.  Modify the VIEW posted previously as follows:
ALTER VIEW [dbo].[vw_PresentationTimes]

AS

SELECT  w.Presenter,
        t.PresentationTime
FROM    dbo.PresentationTimes t
        LEFT JOIN dbo.Workshops$ w ON t.PresentationTime = DATEPART(hour, w.[DATETIME]) * 60 + DATEPART(minute, w.[DATETIME])

Open in new window


2. Execute the following script:
DECLARE @FromTime smallint,
	@ToTime smallint,
	@SQL nvarchar(4000),
	@Select nvarchar(2000),
	@SubSelect nvarchar(2000)

SELECT	@FromTime = 480,		-- 8AM
	@ToTime = 1020			-- 5PM

SELECT  @Select = ISNULL(@Select + ',', '') + 'CASE ' + QUOTENAME(PresentationTime) + ' WHEN 1 THEN ''Scheduled'' ELSE ''X'' END ' +
		QUOTENAME(CONVERT(varchar(5), DATEADD(minute, PresentationTime, 0), 108)),
        @SubSelect = ISNULL(@SubSelect + ',', '') + 'SUM(CASE WHEN PresentationTime = ' + CAST(PresentationTime AS varchar(4)) + ' THEN 1 ELSE 0 END) ' +
		QUOTENAME(PresentationTime) + ' '
FROM    dbo.PresentationTimes
WHERE   PresentationTime BETWEEN @FromTime AND @ToTime

SET @SQL = 
	'SELECT Presenter, ' + @Select + ' FROM (SELECT Presenter, ' + @SubSelect + 
	'FROM  vw_PresentationTimes ' +
	'WHERE PresentationTime BETWEEN ' + CAST(@FromTime AS varchar(4)) + ' AND ' + CAST(@ToTime AS varchar(4)) +
	'      AND Presenter IS NOT NULL ' +
	'GROUP BY Presenter) a'

-- PRINT @SQL           -- uncomment this line to see the SQL Script
EXEC (@SQL)

Open in new window


The output looks like this:
Presenter	08:00	08:30	09:00	09:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00
Presenter1	Scheduled	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
Presenter2	Scheduled	X	X	X	X	X	X	X	X	X	Scheduled	X	X	X	Scheduled	X	X	X	X
Presenter3	X	Scheduled	X	X	X	X	Scheduled	X	X	X	X	X	Scheduled	X	X	X	X	X	X
Presenter4	X	X	Scheduled	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
Presenter5	X	X	Scheduled	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X

Open in new window

0
 
FPLNETAuthor Commented:
Thanks alot!
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.

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