SQL 2005, Query Assistance - 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 2005 Forums

Transact-SQL (2005)

SQL 2005, Query Assistance

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-11-08 : 11:08:29

I need help with the below mentioned query. My client would like a report that sums the total time a provider see's patients minus any blocked appointments and their Lunch hour. I have it currently deducting blocked appointments, however I am struggling with the appointment Types of Lunch. My Attempt 2 is working somewhat ....

It is only reporting time if an appointment is occupying that slot. If an appointment is not there it is producing a 0. I think im missing something painfully obvious. Any help would be appreciated. I would love to make Attempt 1 work over Attempt 2 if possible. Otherwise, I will be looking up individual Appt Types for every client that wants this report.

ApptTime = datediff(minute, aps.Start, aps.Stop),

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

Attempt 2:
ApptTime = case when sta.appttypeid in (68,129,75) 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
(
('9' IS NOT NULL AND a.ResourceID IN (9)) OR
('9' IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
(NULL 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
(
('9' IS NOT NULL AND a.ResourceID IN (9)) OR
('9' IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
(NULL 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 = datediff(minute, aps.Start, aps.Stop),
--ApptTime = case when sta.appttypeid in (select appttypeid from appttype where name like '%lunch%') then 0 else datediff(minute, aps.Start, aps.Stop) end,
--ApptTime = case when sta.appttypeid in (68,129,75) then 0 else datediff(minute, aps.Start, aps.Stop) end,
df.Listname AS Resource,
Facility= f.ListName,
d.Listname AS DoctorName,
'Available / No Appt Assigned' AS Type,
'** Available **' 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
-- join scheduletimes st on s.scheduleid=st.scheduleid
-- join scheduletimesalloc sta on st.scheduletimesid=sta.scheduletimesid

WHERE --Filter on resource
(
('9' IS NOT NULL AND df.DoctorFacilityId IN (9)) OR
('9' IS NULL)
)
AND
(
(NULL IS NOT NULL AND aps.FacilityId IN (NULL)) OR
(NULL 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]

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-08 : 11:52:05

Thats a monster... What does the appointments table look like?? ie, select top 5 * FROM Appointments.

---------------------------------------------------------
SSRS Kills Kittens.

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-11-08 : 12:00:07

Yes, it is a monster. Not sure what you want me to do with "select top 5 * from Appointments". I can run it and paste it all, but thats not going to help much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 13:40:32

You will really benefit from cleaning your dynamic sql creating engine.

OR NULL IS NULL

will always evaluate as true anyway.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources