FPLNET
asked on
SQL Server query to create calendar grid
Hello. 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),datep
else 'X'
end as '12AM',
case
when 0.51 between datepart(hour,datetime)+co
else 'X'
end as '12:30AM',
case
when 1.01 between datepart(hour,datetime)+co
else 'X'
end as '1AM',
case
when 1.51 between datepart(hour,datetime)+co
else 'X'
end as '1:30AM',
case
when 2.01 between datepart(hour,datetime)+co
else 'X'
end as '2AM',
case
when 2.51 between datepart(hour,datetime)+co
else 'X'
end as '2:30AM',
case
when 3.01 between datepart(hour,datetime)+co
else 'X'
end as '3AM',
case
when 3.51 between datepart(hour,datetime)+co
else 'X'
end as '3:30AM',
case
when 4.01 between datepart(hour,datetime)+co
else 'X'
end as '4AM',
case
when 4.51 between datepart(hour,datetime)+co
else 'X'
end as '4:30AM',
case
when 5.01 between datepart(hour,datetime)+co
else 'X'
end as '5AM',
case
when 5.51 between datepart(hour,datetime)+co
else 'X'
end as '5:30AM',
case
when 6.01 between datepart(hour,datetime)+co
else 'X'
end as '6AM',
case
when 6.51 between datepart(hour,datetime)+co
else 'X'
end as '6:30AM',
case
when 7.01 between datepart(hour,datetime)+co
else 'X'
end as '7AM',
case
when 7.51 between datepart(hour,datetime)+co
else 'X'
end as '7:30AM',
case
when 8.01 between datepart(hour,datetime)+co
else 'X'
end as '8AM',
case
when 8.51 between datepart(hour,datetime)+co
else 'X'
end as '8:30AM',
case
when 9.01 between datepart(hour,datetime)+co
else 'X'
end as '9AM',
case
when 9.51 between datepart(hour,datetime)+co
else 'X'
end as '9:30AM',
case
when 10.01 between datepart(hour,datetime)+co
else 'X'
end as '10AM',
case
when 10.51 between datepart(hour,datetime)+co
else 'X'
end as '10:30AM',
case
when 11.01 between datepart(hour,datetime)+co
else 'X'
end as '11AM',
case
when 11.51 between datepart(hour,datetime)+co
else 'X'
end as '11:30AM',
case
when 12.01 between datepart(hour,datetime)+co
else 'X'
end as '12PM',
case
when 12.51 between datepart(hour,datetime)+co
else 'X'
end as '12:30PM',
case
when 13.01 between datepart(hour,datetime)+co
else 'X'
end as '1PM',
case
when 13.51 between datepart(hour,datetime)+ convert(decimal(9,2),datep
else 'X'
end as '1:30PM',
case
when 14.01 between datepart(hour,datetime)+co
else 'X'
end as '2PM',
case
when 14.51 between datepart(hour,datetime)+co
else 'X'
end as '2:30PM',
case
when 15.01 between datepart(hour,datetime)+co
else 'X'
end as '3PM',
case
when 15.51 between datepart(hour,datetime)+co
else 'X'
end as '3:30PM',
case
when 16.01 between datepart(hour,datetime)+co
else 'X'
end as '4PM',
case
when 16.51 between datepart(hour,datetime)+co
else 'X'
end as '4:30PM',
case
when 17.01 between datepart(hour,datetime)+co
else 'X'
end as '5PM',
case
when 17.51 between datepart(hour,datetime)+co
else 'X'
end as '5:30PM',
case
when 18.01 between datepart(hour,datetime)+co
else 'X'
end as '6PM',
case
when 18.51 between datepart(hour,datetime)+co
else 'X'
end as '6:30PM',
case
when 19.01 between datepart(hour,datetime)+co
else 'X'
end as '7PM',
case
when 19.51 between datepart(hour,datetime)+co
else 'X'
end as '7:30PM',
case
when 20.01 between datepart(hour,datetime)+co
else 'X'
end as '8PM',
case
when 20.51 between datepart(hour,datetime)+co
else 'X'
end as '8:30PM',
case
when 21.01 between datepart(hour,datetime)+co
else 'X'
end as '9PM',
case
when 21.51 between datepart(hour,datetime)+co
else 'X'
end as '9:30PM',
case
when 22.01 between datepart(hour,datetime)+co
else 'X'
end as '10PM',
case
when 22.51 between datepart(hour,datetime)+co
else 'X'
end as '10:30PM',
case
when 23.01 between datepart(hour,datetime)+co
else 'X'
end as '11PM',
case
when 23.51 between datepart(hour,datetime)+co
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.
ASKER
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
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
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:
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.
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
) DECLARE @PresentationTime smallint
SET @PresentationTime = 0
WHILE @PresentationTime < 1440
BEGIN
INSERT dbo.PresentationTimes(PresentationTime)
VALUES (@PresentationTime)
SET @PresentationTime = @PresentationTime + 30
ENDCREATE 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 appropriateIf 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks alot!
Please post the schema to your table (CREATE TABLE) and some sample data.