Solved

Microsoft, SQL, SQL 2000, Query Assistance

Posted on 2007-11-14
5
559 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
ID: 20282989
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
ID: 20283000
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
ID: 20283080
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
ID: 20284710
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
ID: 20284914
Thanks a million!!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 47
xpath sql query 2008 8 42
SQL Error in WHERE Clause 5 39
Help with SQL Server Stoplist 2 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

912 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

21 Experts available now in Live!

Get 1:1 Help Now