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 2000 Forums
 Transact-SQL (2000)
 SQL Time Calculation Help (Complex Query)

Author  Topic 

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-11-13 : 17:05:34
I need help with calculating time. This query has to do with a Doctors Appointment. In the schedule, most of the time, an appointment is booked and is by default placed into Column 1. If the provider elects to double book their time, the second appointment hits the column 2. It is possible to have up to 4 appointments in one slot. This is why we wait forever at the Doctors office - appointments get overbooked because patients cancel or no show frequently and the provider would rather have the patient wait then risk no appointment at all.

My issue is this ....

If I have 2 patients scheduled from 10:00 am - 10:15 am and I cancel the patient in column 1, the system forces the patient out of Column 1. This is so another patient can be scheduled into this slot if needed.

What I was intending to do is count the time in this time slot one time not twice. I am looking to get a summary of time the provider was scheduled to see patients. So if the Provider was scheduled to see patients from 8 am to noon and had patients doublebooked for every time slot, I only want to overall time he saw patients regardless of the doublebooking, triplebooking or quadruplebooking. So the total time for my example should be 4 hours not 8 hours.

Example:

With the example earlier (the two visits scheduled from 10:00 - 10:15) . Because I canceled one, 3 records are returned. One has the cancel, one is now blank (replacement of the cancel) and the other patient. What I would like is because each share the same start and stop time, is take count this as one distinct time and sum up the entire schedule.

A.ApptStart = 2007-11-13 10:00:00.000
A.ApptStop = 2007-11-13 10:15:00.000

I hope this makes sense. If not, please ask for further clarification. Any help is appreciated. Please note, I know the filters look strange (this is how the third party application formats them - the report works TRUST ME). I dont like how it looks either (the filters that is). I know the NULL IS NOT NULL ... is strange ... but thats not the issue).

My current query:

/*Schedule Summary*/
SET NOCOUNT ON

--Patient Appointments

DECLARE @Today DATETIME
DECLARE @Tomorrow DATETIME
SET @Today = '11/13/2007'
SET @Tomorrow = dateadd(d, 1, '11/13/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/13/2007','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('11/13/2007','1/1/3000'))
AND --Filter on doctor
(
('414' IS NOT NULL AND a.ResourceID IN (414)) OR
('414' 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/13/2007','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('11/13/2007','1/1/3000'))
AND --Filter on doctor
(
('414' IS NOT NULL AND a.ResourceID IN (414)) OR
('414' 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 = 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,
---'Available / No Appt Assigned' 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',
---'** 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
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
(
('414' IS NOT NULL AND df.DoctorFacilityId IN (414)) OR
('414' 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]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 17:15:33
Seems like you could force a count of patients by 1 time slot using Case/When in the select statement...hard to say without understanding the underlying table structures..

..any way to post the DDL structures of the source tables and some redacted data samples such as explained in the below link?

The code unformatted and without references to check or use, it may be challenging to get proper help.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page
   

- Advertisement -