Solved

Microsoft, SQL, SQL 2000, Query Assistance

Posted on 2007-11-14
5
562 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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