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.000A.ApptStop = 2007-11-13 10:15:00.000I 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 AppointmentsDECLARE @Today DATETIMEDECLARE @Tomorrow DATETIMESET @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.MedlistsIdWHERE 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 AppointmentsSELECT 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.MedlistsIdWHERE 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 ALLSELECT 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.MedlistsIdleft join (select sta.timestart,sta.timestop,st.scheduleid,sta.appttypeidfrom scheduletimesalloc stajoin 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.timestopWHERE --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 <> -1ORDER BY [Resource], [ResourceStart] |
|