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

Microsoft, SQL, SQL 2000, Query Assistance

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
Jeff S
Asked:
Jeff S
  • 2
  • 2
1 Solution
 
JimFiveCommented:
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
 
imitchieCommented:
in the 3 places where you have
WHERE name like '%lunch%'
change to
WHERE name like '%lunch% OR name LIKE '%meeting%'
0
 
Jeff SAuthor Commented:
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
 
imitchieCommented:
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
 
Jeff SAuthor Commented:
Thanks a million!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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