Solved

Microsoft, SQL, SQL 2000, Query Assistance

Posted on 2007-11-14
5
556 Views
Last Modified: 2008-02-01
I need the following to also look where name like '%meeting%'. I tried OR LIKE '%meeting%' and it didnt like me. I need some syntax assistance please!!

ApptTime = case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%') then 0 else datediff(minute, aps.Start, aps.Stop) end,

My 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%') 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%') 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%') 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
--      and cast('1/1/1900 '+cast(datepart(hour,aps.start) as varchar(10))+':'+cast(datepart(minute,aps.start) as varchar(10)) as datetime) between st.timestart and st.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 st.timestart and st.timestop
) 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]
0
Comment
Question by:Jeff S
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
If you want only those records that are like '%meeting%' you need to use AND <fieldname> like '%meeting%'

Where the <fieldname> is the column you are comparing to.

--
JimFive
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
in the 3 places where you have
WHERE name like '%lunch%'
change to
WHERE name like '%lunch% OR name LIKE '%meeting%'
0
 
LVL 7

Author Comment

by:Jeff S
Comment Utility
imitchie:

I need help on these fields now (Specific to the 'when 0 then 'Lunch' ). It used to be exclusive to Lunch but now I have Meeting. So how could I have it name them as appropriate?

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',
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
case case when tmp.appttypeid in (select appttypeid from appttype where name like '%lunch%') then 0
when tmp.appttypeid in (select appttypeid from appttype where name like '%meeting%') then -1
else datediff(minute, aps.Start, aps.Stop) end when 0 then 'Lunch' when -1 then 'Meeting' else 'Available / No Appt Assigned' end AS Type,

etc
0
 
LVL 7

Author Comment

by:Jeff S
Comment Utility
Thanks a million!!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now