June 25, 2003 at 5:05 pm
Hello,
I have set up the following query designed to produce a Cross-Tab report:
SELECT AptDate, AptTime,
COUNT(CASE WHEN SchedName = 'Monica' THEN Appt ELSE NULL END) AS Monica,
COUNT(CASE WHEN SchedName = 'Olivia' THEN Appt ELSE NULL END) AS Olivia,
COUNT(CASE WHEN SchedName = 'Jaime' THEN Appt ELSE NULL END) AS Jaime,
COUNT(CASE WHEN SchedName = 'Lizeth' THEN Appt ELSE NULL END) AS Lizeth
FROM tblCSMainData
GROUP BY AptDate, AptTime
The returned records show an AptDate, AptTime, and a count of each 'Appt'(Appointment) where the 'SchedName' is the name expressed in the CASE statements above.
Instead of expressing the COUNT of the resulting CASE statements, I need to express the contents of 'Appt' which is a VARCHAR field filled with text values. Using COUNT in this case will always give me a one or zero result, but I need to see the contents of the 'Appt' field.
What can I do to express the data in the field instead of expressing COUNT?
Thanks for your help!
CSDunn
June 25, 2003 at 9:11 pm
One solution is to use a function. Eg:
create table tblCSMainData (
AptDate varchar(10),
AptTime varchar(5),
SchedName varchar(100),
Appt varchar(100)
)
go
insert tblCSMainData values ('2003-06-26','10:00', 'Monica', 'Mrs Jeffreys')
insert tblCSMainData values ('2003-06-26','10:00', 'Monica', 'Mr Brown')
insert tblCSMainData values ('2003-06-26','10:00', 'Monica', 'Mrs Jones')
insert tblCSMainData values ('2003-06-26','10:30', 'Monica', 'Ms Smith')
insert tblCSMainData values ('2003-06-26','10:00', 'Olivia', 'Ms Black')
insert tblCSMainData values ('2003-06-26','10:00', 'Olivia', 'Ms Braun')
insert tblCSMainData values ('2003-06-26','10:00', 'Jaime', 'Mrs Grey')
insert tblCSMainData values ('2003-06-26','10:30', 'Jaime', 'Ms Gray')
insert tblCSMainData values ('2003-06-26','10:30', 'Lizeth', 'Mrs White')
insert tblCSMainData values ('2003-06-26','11:00', 'Olivia', 'Ms Block')
insert tblCSMainData values ('2003-06-26','11:00', 'Olivia', 'Mrs Blick')
insert tblCSMainData values ('2003-06-26','11:00', 'Olivia', 'Mr Blek')
insert tblCSMainData values ('2003-06-26','11:00', 'Olivia', 'Miss Informed')
go
CREATE FUNCTION SchedAppointments
(@AptDate varchar(10), @AptTime varchar(5), @SchedName varchar(100))
RETURNS varchar(1000)
AS
BEGIN
declare @appts varchar(1000)
set @appts = ''
select @appts = @appts + case when @appts = '' then '' else ', ' end
+ Appt
from tblCSMainData
where AptDate = @AptDate
and AptTime = @AptTime
and SchedName = @SchedName
return @appts
END
GO
SELECT
AptDate, AptTime
,dbo.SchedAppointments(AptDate, AptTime, 'Monica') AS Monica
,dbo.SchedAppointments(AptDate, AptTime, 'Olivia') AS Olivia
,dbo.SchedAppointments(AptDate, AptTime, 'Jaime') AS Jaime
,dbo.SchedAppointments(AptDate, AptTime, 'Lizeth') AS Lizeth
FROM
(SELECT DISTINCT AptDate, AptTime FROM tblCSMainData) DatesAndTimes
order by 1,2,3
Cheers,
- Mark
June 26, 2003 at 7:13 am
SELECT AptDate, AptTime,
MAX(CASE WHEN SchedName = 'Monica' THEN Appt ELSE '' END) AS Monica,
MAX(CASE WHEN SchedName = 'Olivia' THEN Appt ELSE '' END) AS Olivia,
MAX(CASE WHEN SchedName = 'Jaime' THEN Appt ELSE '' END) AS Jaime,
MAX(CASE WHEN SchedName = 'Lizeth' THEN Appt ELSE '' END) AS Lizeth
FROM tblCSMainData
GROUP BY AptDate, AptTime
Far away is close at hand in the images of elsewhere.
Anon.
June 26, 2003 at 9:51 am
Thanks for your help!
CSDunn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply