Per my clients needs, they need to query for all canceled appointments (see query below) for working query. They then need to see if the patients within this list re-scheduled an appointment within 5 days of the cancelation. What I ultimately need is another column added to my dataset that is a simple yes or no statement. Yes, this patient rescheduled within 5 days or no they did not reschedule their appt.A scheduled appointment will be the same as below, just changing the <> 0 to = 0 on the ISNULL ( ap.Canceled, 0 ) in the where clause. What would be the most efficient and cleanest way to write this? A subquery?Current working Query, needing one additional field for those patients that re-scheduled within the 5 days. -- Canceled AppointmentsSET NOCOUNT ON;SELECT ap.ApptStart , aset.ApptSetId , dbo.asGetApptSetName ( ISNULL ( aset.ApptSetId , 0 ) , ap.AppointmentsId ) AS ApptSetName , ac.ApptChainId , ac.Name AS ApptChainName , ml.Description AS Status , ap.FacilityId , ap.OwnerId , ap.ApptKind , ap.ResourceId , at.Name AS Type , df2.ListName AS Facility , ISNULL ( pp.Last , '' ) + ', ' + ISNULL ( pp.First , '' ) AS PatientName , pp.PatientId , ISNULL ( pp.MedicalRecordNumber , '' ) AS MRN , ISNULL ( pp.SchoolName , '' ) AS DentalId , CONVERT ( VARCHAR ( 2 ) , DATEPART ( hour , ap.ApptStart )) AS StartHour , CONVERT ( VARCHAR ( 2 ) , DATEPART ( minute , ap.ApptStart )) AS StartMinute , df.ListName AS Resource , ic.ListName AS InsuranceCarrier FROM Appointments ap LEFT OUTER JOIN ApptChain ac ON ap.ApptChainId = ac.ApptChainId LEFT OUTER JOIN ApptSet aset ON ap.ApptSetId = aset.ApptSetId LEFT OUTER JOIN MedLists ml ON ap.ApptStatusMId = ml.MedListsId LEFT OUTER JOIN ApptType at ON ap.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN DoctorFacility df ON ap.ResourceId = df.DoctorFacilityId LEFT OUTER JOIN DoctorFacility df2 ON ap.FacilityId = df2.DoctorFacilityId LEFT OUTER JOIN PatientProfile pp ON ap.OwnerId = pp.PatientProfileId LEFT OUTER JOIN CasesInsurance ci ON ISNULL ( ap.CasesId , 0 ) = ISNULL ( ci.CasesId , 0 ) AND ci.PatientProfileId = pp.PatientProfileId AND ci.OrderForClaims = 1 LEFT OUTER JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId LEFT OUTER JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId WHERE ap.ApptKind = 1 AND ISNULL ( ap.Canceled , 0 ) <> 0 AND ap.ApptStart >= ISNULL ( '01/01/2015' , '1/01/1900' ) AND ap.ApptStart < DATEADD ( day , 1 , ISNULL ( '04/06/2015' , '1/01/3000' ))
Help with a SQL subquery
Posted - 2015-04-06 : 22:13:59