Case Statement Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case Statement Help

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-11-14 : 15:17:36

Today, I added in 'OR name LIKE '%meeting%' to the following:

case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0 else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' else 'Available / No Appt Assigned' end AS Type,

case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0 else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' else '** Available **' end AS 'Patient Name',

My issue is, now Its only printing the Name as Lunch because it = 0. I need it to name as Lunch when the name like Lunch and Meeting when the name like Meeting. Any help is appreciated.

Full Query:

/*Schedule Summary*/
SET NOCOUNT ON

--Patient Appointments

DECLARE @Today DATETIME
DECLARE @Tomorrow DATETIME
SET @Today = '11/14/2007'
SET @Tomorrow = dateadd(d, 1, '11/14/2007')

SELECT DISTINCT
Date=convert(datetime,convert(char(12),a.ApptStart,1)),
convert(datetime,a.ApptStart) AS ResourceStart,
convert(datetime,a.ApptStop) AS ApptStop,
ApptTime = datediff(minute, a.ApptStart, a.ApptStop),
dfr.Listname AS Resource,
Facility= dff.Listname,
dfd.Listname AS DoctorName,
ISNULL(at.NAME,'No Appointment Type') AS Type,
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', '+ ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) AS 'Patient Name',
CASE WHEN a.canceled = '1' THEN NULL ELSE a.appointmentsid END AS AppointmentsID,
ApptKind = 1,
a.canceled AS Canceled,
CASE WHEN a.canceled IS NULL THEN NULL ELSE ml.Description END as [Cancel Status],
CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column]

FROM Appointments a
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN ApptSlot aps ON a.AppointmentsId = aps.ApptId
LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId

WHERE ApptKind = 1 /*AND ISNULL(Canceled,0) = 0*/ AND
a.ApptStart >= ISNULL('11/14/2007','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('11/14/2007','1/1/3000'))
AND --Filter on doctor
(
('5' IS NOT NULL AND a.ResourceID IN (5)) OR
('5' IS NULL)
)
AND --Filter on facility
(
('58' IS NOT NULL AND a.FacilityID IN (58)) OR
('58' IS NULL)
)

UNION ALL

--Resource Appointments

SELECT DISTINCT
Date=convert(datetime,convert(char(12),a.ApptStart,1)),
convert(datetime,a.ApptStart) AS ResourceStart,
convert(datetime,a.ApptStop) AS ApptStop,
ApptTime = datediff(minute, a.ApptStart, a.ApptStop),
dfr.Listname AS Resource,
Facility= dff.Listname,
NULL AS DoctorName,
ISNULL(at.NAME,'No Appointment Type') AS Type,
CASE WHEN ApptKind = 3 THEN '<Doctor/Resource>'
WHEN ApptKind = 5 THEN '<Block Out>'
ELSE '<Other>' END AS 'Patient Name',
NULL AS AppointmentsID,
a.ApptKind,
NULL AS Canceled,
NULL as [Cancel Status],
CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column]

FROM Appointments a
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN ApptSlot aps ON a.AppointmentsId = aps.ApptId
LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId

WHERE ApptKind <> 1 AND
a.ApptStart >= ISNULL('11/14/2007','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('11/14/2007','1/1/3000'))
AND --Filter on doctor
(
('5' IS NOT NULL AND a.ResourceID IN (5)) OR
('5' IS NULL)
)
AND --Filter on facility
(
('58' IS NOT NULL AND a.FacilityID IN (58)) OR
('58' IS NULL)
)

UNION ALL

SELECT DISTINCT
Date=convert(datetime,convert(char(12),Start,1)),
convert(datetime,aps.Start) AS ResourceStart,
convert(datetime,aps.Stop) AS ApptStop,
ApptTime = case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0
else datediff(minute, aps.Start, aps.Stop) end,
df.Listname AS Resource,
Facility= f.ListName,
d.Listname AS DoctorName,
case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0
else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' else 'Available / No Appt Assigned' end AS Type,
case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0
else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' else '** Available **' end AS 'Patient Name',
a.appointmentsid AS AppointmentsID,
1 as ApptKind,
a.canceled AS Canceled,
ml.Description as [Cancel Status],
CASE WHEN aps.ListOrder IS NULL THEN 'Overbooked' ELSE CAST(aps.ListOrder AS Varchar(10)) END AS [Column]

FROM ApptSlot aps
JOIN Schedule s ON aps.ScheduleId = s.ScheduleId
JOIN DoctorFacility df ON s.DoctorResourceId = df.DoctorFacilityId
JOIN DoctorFacility f ON aps.FacilityId = f.DoctorFacilityId
JOIN DoctorFacility d ON s.DoctorResourceId = d.DoctorFacilityId
LEFT JOIN Appointments a ON aps.apptid = a.appointmentsID
LEFT JOIN Medlists ml ON a.ApptStatusMId = ml.MedlistsId
left join
(
select sta.timestart,sta.timestop,st.scheduleid,sta.appttypeid
from scheduletimesalloc sta
join scheduletimes st on st.scheduletimesid=sta.scheduletimesid
) tmp on tmp.scheduleid=s.scheduleid
and cast('1/1/1900 '+cast(datepart(hour,aps.start) as varchar(10))+':'+cast(datepart(minute,aps.start) as varchar(10)) as datetime) between tmp.timestart and tmp.timestop
and cast('1/1/1900 '+cast(datepart(hour,aps.stop) as varchar(10))+':'+cast(datepart(minute,aps.stop) as varchar(10)) as datetime) between tmp.timestart and tmp.timestop

WHERE --Filter on resource
(
('5' IS NOT NULL AND df.DoctorFacilityId IN (5)) OR
('5' IS NULL)
)
AND
(
('58' IS NOT NULL AND aps.FacilityId IN (58)) OR
('58' IS NULL)
)
AND (Start >= @Today OR @Today IS NULL)
AND (Start < @Tomorrow OR @Tomorrow IS NULL)
AND ApptId IS NULL
AND APS.ListOrder <> -1

ORDER BY [Resource], [ResourceStart]

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-11-14 : 21:13:35

quote:

Originally posted by JeffS23

Today, I added in 'OR name LIKE '%meeting%' to the following:

case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0 else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' else 'Available / No Appt Assigned' end AS Type,

case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0 else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' else '** Available **' end AS 'Patient Name',

Just an observation...

why like this...

case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 0 else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' else 'Available / No Appt Assigned' end AS Type,

Instead of this?

case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%'OR name LIKE '%meeting%') then 'Lunch' else 'Available / No Appt Assigned' end

likewise also on your second case statement.

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources