August 2, 2011 at 9:23 am
I have this query attached below, that simply will not run, I have cancelled it after 40 mins as thats just way too wrong for a query.
Is there a way that I can simplfy it and make it run within a reasonable timescale. I will be using it to insert into a StoredProc and then have a table that populates on a web page
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,c13.optiontext AnswerOption
INTO #SequenceA
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 0
--**************************
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,a16.dateanswer ExpiryDate
INTO #SequenceB
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 1
--**************************
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,c13.optiontext AnswerOption
INTO #SequenceC
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 3
--**************************
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,c13.optiontext AnswerOption
INTO #SequenceD
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 4
--**************************
SELECT cr.id ReferralID
,a5.id UAID
,a10.authoringiauthoringd AuthDate
,c6.nameforename+' '+c6.namesurname AuthName
,A.AnswerOption Undischarged
,B.ExpiryDate
,CASE
WHEN B.ExpiryDate IS NULL THEN NULL
WHEN DATEDIFF(dd,CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME), B.ExpiryDate) < 0 THEN 'Yes'
ELSE 'No'
END ActionExpired
,C.AnswerOption Actions
,D.AnswerOption MSKResultsForReview
,CASE
WHEN A.AnswerOption != 'Consultant Action Required' THEN 'Undischarged - ' + + A.AnswerOption
WHEN D.AnswerOption IS NULL THEN A.AnswerOption
WHEN A.AnswerOption = 'Consultant Action Required' THEN 'MSK Results Review - ' + + D.AnswerOption
END QuestionAnswer
INTO #UA
FROM care_catsreferral cr
LEFT OUTER JOIN asse_patientassessi a5 ON cr.carecontex = a5.carecontex
LEFT OUTER JOIN asse_patientassess2 a6 ON a5.assessment = a6.id
LEFT OUTER JOIN asse_userassessment a7 ON a6.userassess = a7.id
LEFT OUTER JOIN asse_patientassess3 a8 ON a6.id = a8.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a9 ON a8.id = a9.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a10 ON a9.id = a10.asse_assessmentpatq_patientans
LEFT OUTER JOIN core_hcp c5 ON a10.authoringiauthoringh = c5.id
LEFT OUTER JOIN core_memberofstaff c6 ON c5.id = c6.hcp
LEFT OUTER JOIN core_patient c12 ON cr.patient = c12.id
LEFT OUTER JOIN core_patient_c_identifi c13 ON c12.id = c13.id
LEFT OUTER JOIN #SequenceA A ON a5.id = A.UAID
LEFT OUTER JOIN #SequenceB B ON a5.id = B.UAID
LEFT OUTER JOIN #SequenceC C ON a5.id = C.UAID
LEFT OUTER JOIN #SequenceD D ON a5.id = D.UAID
WHERE a10.authoringiauthoringd IS NOT NULL
AND a7.name LIKE '%Undischarged%'
AND c13.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116', '9111111194',
'7048481595', '4111111149', '9990019924', '3111111113', '4111111114', '5111111115',
'6111111116', '7111111117', '8777777778', '4523653987','9990377952')
--**************************
SELECT DISTINCT c1.id AS ReferralID
,MAX(UA.UAID) OVER(PARTITION BY c1.id) UAID
,MAX(UA.AuthDate) OVER(PARTITION BY c1.id) AuthDate
,MAX(UA.QuestionAnswer) OVER(PARTITION BY c1.id) QuestionAnswer
,MAX(UA.ExpiryDate) OVER(PARTITION BY c1.id) ActionExpiryDate
,CASE
WHEN MAX(UA.ExpiryDate) OVER(PARTITION BY c1.id) IS NULL THEN NULL
WHEN DATEDIFF(dd,CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME), MAX(UA.ExpiryDate) OVER(PARTITION BY c1.id)) < 0 THEN 'Yes'
ELSE 'No'
END ActionExpired
,(
SELECT TOP 1 REPLACE(REPLACE(CONVERT(VARCHAR(2560),a16.stringansw), CHAR(10),' '), CHAR(13),' ') Comments
FROM care_catsreferral c11
LEFT OUTER JOIN asse_patientassessi a1 ON c11.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 2
AND c11.id = c1.id
ORDER BY a1.id DESC
) ActionComments
,MAX(UA.Actions) OVER(PARTITION BY c1.id) AnyActions
INTO #UserAssesment
FROM care_catsreferral c1
LEFT OUTER JOIN core_patient c2 ON c1.patient = c2.id
LEFT OUTER JOIN core_patient_c_identifi c3 ON c2.id = c3.id
LEFT OUTER JOIN #UA UA ON c1.id = UA.ReferralID
WHERE c3.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116', '9111111194', '7048481595',
'4111111149', '9990019924', '3111111113', '4111111114', '5111111115', '6111111116', '7111111117',
'8777777778', '4523653987','9990377952')
AND UA.UAID IS NOT NULL
--**************************
SELECT ReferralID
,MAX(ExpiryDate) BreachExpiryDate
,MAX(ActionRequired) BreachActionReq
,MAX(Comments) BreachComments
INTO #BreachUA
FROM
(
SELECT c1.id ReferralID
,a12.dateanswer ExpiryDate
,c6.optiontext ActionRequired
,a12.stringansw Comments
,a11.authoringiauthoringd EntryDate
,MAX(a11.authoringiauthoringd)
OVER(PARTITION BY c1.id) LastEntry
FROM asse_patientassessi a2
LEFT OUTER JOIN care_catsreferral c1 ON a2.carecontex = c1.carecontex
LEFT OUTER JOIN asse_patientassess2 a3 ON a2.assessment = a3.id
LEFT OUTER JOIN asse_userassessment a4 ON a3.userassess = a4.id
LEFT OUTER JOIN asse_patientassess3 a5 ON a3.id = a5.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a5.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a11 ON a7.id = a11.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_patientanswerd a12 ON a11.id = a12.asse_patientassess4_answerdeta
LEFT OUTER JOIN asse_patientanswerd_multiselec a15 ON a12.id = a15.from_side
LEFT OUTER JOIN core_answeroption c6 ON a15.to_side = c6.id
WHERE a4.name LIKE '%Advised Breach of 56 Day KPI%'
) TEMP
WHERE EntryDate = LastEntry
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MIN(FirstApptDate) ApptDate
,MIN(FirstApptType) ApptType
,MIN(FirstApptLocation) ApptLocation
INTO #FirstAppts
FROM
(
SELECT care_catsreferral_appointmen ReferralID
,SBA.appointmen FirstApptDate
,a1.name FirstApptType
,l.name FirstApptLocation
,MIN(SBA.appointmen) OVER(PARTITION BY SBA.care_catsreferral_appointmen) FirstRefAppt
FROM schl_booking_appoin AS SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
LEFT OUTER JOIN schl_sch_session sss ON SBA.c_sessi = sss.id
LEFT OUTER JOIN core_location l ON sss.schlocatio = l.id
WHERE SBA.lkp_apptstatus IN (-1407, -1408)
) TEMP
WHERE FirstApptDate = FirstRefAppt
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MAX(FirstApptDate) ApptDate
,MAX(FirstApptType) ApptType
,MAX(FirstApptLocation) ApptLocation
INTO #LastAppts
FROM
(
SELECT care_catsreferral_appointmen ReferralID
,SBA.appointmen FirstApptDate
,a1.name FirstApptType
,l.name FirstApptLocation
,MAX(SBA.appointmen) OVER(PARTITION BY SBA.care_catsreferral_appointmen) FirstRefAppt
FROM schl_booking_appoin AS SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
LEFT OUTER JOIN schl_sch_session sss ON SBA.c_sessi = sss.id
LEFT OUTER JOIN core_location l ON sss.schlocatio = l.id
WHERE SBA.lkp_apptstatus IN (-1407, -1408)
) TEMP
WHERE FirstApptDate = FirstRefAppt
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MIN(FirstApptDate) ApptDate
,MIN(FirstApptType) ApptType
,MIN(FirstApptLocation) ApptLocation
INTO #FutureAppts
FROM
(
SELECT care_catsreferral_appointmen ReferralID
,SBA.appointmen FirstApptDate
,SBA.apptstartt FirstApptTime
,a1.name FirstApptType
,l.name FirstApptLocation
,MIN(SBA.appointmen + sba.apptstartt) OVER(PARTITION BY SBA.care_catsreferral_appointmen) FirstRefAppt
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sch_session sss ON SBA.c_sessi = sss.id
LEFT OUTER JOIN core_location l ON sss.schlocatio = l.id
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE SBA.appointmen + sba.apptstartt >= GETDATE()
AND SBA.lkp_apptstatus <> -568
AND a1.name IS NOT NULL
) TEMP
WHERE FirstApptDate + FirstApptTime = FirstRefAppt
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MIN(ApptDate) InvestApptDate
,MIN(ServiceName) InvestApptType
INTO #FutureInvestigations
FROM
(
SELECT DISTINCT c1.id ReferralID
,s1.appointmen ApptDate
,s1.apptstartt ApptTime
,c5.servicenam ServiceName
,MIN(s1.appointmen + s1.apptstartt) OVER(PARTITION BY c1.id) FirstAppt
FROM care_catsreferral c1
LEFT OUTER JOIN schl_booking_appoin s1 ON c1.id = s1.care_catsreferral_appointmen
LEFT OUTER JOIN ocrr_ocsorder o1 ON c1.id = o1.care_catsreferral_investigat
LEFT OUTER JOIN schl_sch_session s2 ON s1.c_sessi = s2.id
LEFT OUTER JOIN schl_sess_slot s3 ON s1.id = s3.appointmen
LEFT OUTER JOIN applookup_instance a1 ON s1.lkp_apptstatus = a1.id
LEFT OUTER JOIN core_location c2 ON s2.schlocatio = c2.id
LEFT OUTER JOIN core_activity c3 ON s3.activity = c3.id
LEFT OUTER JOIN ocrr_orderinvestiga o2 ON o1.id = o2.orderdetai
LEFT OUTER JOIN ocrr_investigation o3 ON o2.investigat = o3.id
LEFT OUTER JOIN ocrr_locsvcprovsys o4 ON o3.providerse = o4.id
LEFT OUTER JOIN core_locationservic c4 ON o4.locationse = c4.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
WHERE s1.appointmen + s1.apptstartt >= GETDATE()
AND c3.name LIKE '%Investig%'
AND s1.lkp_apptstatus <> -568
AND c5.servicenam NOT IN ('physiotherapy', 'Podiatry')
) TEMP
WHERE ApptDate + ApptTime = FirstAppt
GROUP BY ReferralID
--**************************
SELECT DISTINCT c1.id ReferralID
,CASE
WHEN c5.servicenam IS NULL THEN o5.name
ELSE c5.servicenam
END Investigation
INTO #Investigations
FROM care_catsreferral c1
LEFT OUTER JOIN core_patient c2 ON c1.patient = c2.id
LEFT OUTER JOIN ocrr_ocsorder o1 ON c1.id = o1.care_catsreferral_investigat
LEFT OUTER JOIN core_patient_c_identifi c3 ON c2.id = c3.id
LEFT OUTER JOIN ocrr_orderinvestiga o2 ON o1.id = o2.orderdetai
LEFT OUTER JOIN ocrr_investigation o3 ON o2.investigat = o3.id
LEFT OUTER JOIN ocrr_orderinvestiga_ordinvstat o6 ON o2.id = o6.id
LEFT OUTER JOIN applookup_instance a5 ON o2.lkp_ordinvcurrordinvstat = a5.id
LEFT OUTER JOIN ocrr_locsvcprovsys o4 ON o3.providerse = o4.id
LEFT OUTER JOIN ocrr_investigationi o5 ON o3.investigat = o5.id
LEFT OUTER JOIN core_locationservic c4 ON o4.locationse = c4.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
LEFT OUTER JOIN applookup_instance a1 ON o5.lkp_category = a1.id
LEFT OUTER JOIN applookup_instance a2 ON o6.lkp_ordinvstat = a2.id
LEFT OUTER JOIN applookup_instance a3 ON o6.lkp_statuschan = a3.id
WHERE a1.[text] <> 'Pathology'
AND a5.[text] NOT IN ('Cancel Request', 'Cancelled')
AND o2.appointmen IS NULL
AND c3.c_val NOT IN ('3111111113', '9990019924', '4111111114', '5111111115',
'6111111116', '7111111117', '7048481595', '3000000003',
'6188490774', '4786214116', '7048628863', '9111111194',
'4523655987')
--**************************
SELECT DISTINCT c1.id AS ReferralID
,MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id) TLTOrderedDate
,DATEADD(dd, 14, MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id)) TLT14Days
,DATEADD(dd, 42, MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id)) TLT42Days
,DATEADD(dd, 126, MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id)) TLT126Days
,DATEDIFF(ww,MIN(c1.triagedate) OVER(PARTITION BY c1.id),GETDATE()) DaysDiff
,MIN(c5.servicenam) OVER(PARTITION BY c1.id) TLTApptType
INTO #TLTBookings
FROM care_catsreferral c1
LEFT OUTER JOIN care_orderinvappt c2 ON c1.id = c2.care_catsreferral_orderinvap
LEFT OUTER JOIN core_patient c6 ON c1.patient = c6.id
LEFT OUTER JOIN ocrr_orderinvestiga o1 ON c2.orderinves = o1.id
LEFT OUTER JOIN schl_booking_appoin s1 ON c2.appointmen = s1.id
LEFT OUTER JOIN ocrr_investigation o2 ON o1.investigat = o2.id
LEFT OUTER JOIN applookup_instance a4 ON o1.lkp_ordinvcurrordinvstat = a4.id
LEFT OUTER JOIN ocrr_investigationi o3 ON o2.investigat = o3.id
LEFT OUTER JOIN ocrr_locsvcprovsys o5 ON o2.providerse = o5.id
LEFT OUTER JOIN core_activity c3 ON o3.activity = c3.id
LEFT OUTER JOIN core_locationservic c4 ON o5.locationse = c4.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
LEFT OUTER JOIN schl_appt_history_s s2 ON s1.currentsta = s2.id
LEFT OUTER JOIN applookup_instance a1 ON s2.lkp_status = a1.id
LEFT OUTER JOIN core_patient_c_identifi c7 ON c6.id = c7.id
WHERE (c3.name LIKE '%Podiatry%'
OR c3.name LIKE '%Physio%')
AND (a1.text IN ('Booked', 'Seen')
OR a1.text is null)
AND a4.text NOT LIKE '%Cancel%'
AND c7.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116',
'7048628863', '9111111194', '7048481595', '4111111149', '9990019924',
'3111111113', '4111111114', '5111111115', '6111111116', '7111111117',
'4523655987')
--**************************
SELECT DISTINCT c1.id ReferralID
,c7.servicenam Specialty
,c6.name Activity
,s1.appointmen ApptDate
INTO #TLTBookingsAppts
FROM care_catsreferral c1
LEFT OUTER JOIN core_patient c2 ON c1.patient = c2.id
LEFT OUTER JOIN schl_booking_appoin s1 ON c1.id = s1.care_catsreferral_appointmen
LEFT OUTER JOIN core_referralletter c4 ON c1.referralde = c4.id
LEFT OUTER JOIN core_patient_c_identifi c3 ON c2.id = c3.id
LEFT OUTER JOIN schl_sess_slot s2 ON s1.id = s2.appointmen
LEFT OUTER JOIN applookup_instance a1 ON s1.lkp_apptstatus = a1.id
LEFT OUTER JOIN core_activity c6 ON s2.activity = c6.id
LEFT OUTER JOIN schl_sch_session s3 ON s2.c_sessi = s3.id
LEFT OUTER JOIN core_services c7 ON s3.service = c7.id
LEFT OUTER JOIN core_location c8 ON s3.schlocatio = c8.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
WHERE a1.text LIKE '%Booked%'
AND (s3.name LIKE '%Physio%'
OR s3.name LIKE '%Podiatry%')
AND c3.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774',
'4786214116', '7048628863', '9111111194', '7048481595',
'4111111149', '9990019924', '3111111113', '4111111114',
'5111111115', '6111111116', '7111111117', '4523655987')
--**************************
SELECT DISTINCT
CASE
WHEN ReportSubStatus = 'Physiotherapy Direct Access' THEN 'Waiting List'
ELSE ReportStatus
END ReportStatus
,ReportSubStatus
,ReceiptStatus
,ReferralID
,NHSNumber
,IsCAB
,DateOfReferral
,WeekOfReferral
,CASE
WHEN ReceiptStatus = 'Backlog' THEN 'Backlog'
ELSE
CASE
WHEN LEN(DATEPART(mm,DateOfReferral)) = 1 THEN '0' + CONVERT(CHAR(1),DATEPART(mm,DateOfReferral))
+ '/' + CONVERT(CHAR(4),DATEPART(yyyy,DateOfReferral))
ELSE CONVERT(CHAR(2),DATEPART(mm,DateOfReferral)) + '/' + CONVERT(CHAR(4),DATEPART(yyyy,DateOfReferral))
END
END MonthOfReferral
,WeeksWaitingToday
,ReferralStatus
,Specialty
,Consultant
,TLTName
,FirstAttendedApptDate
,FirstAttendedApptType
,FirstAttendedApptLocation
,LastAttendedApptDate
,LastAttendedApptType
,LastAttendedApptLocation
,FutureApptDate
,FutureApptType
,FutureApptLocation
,TimeUntilFutureAppt
,LastApptDNA
,LastApptCanx
,NoOfPreviousTLT
,NoOfFutureTLT
,NoOfFollowUps
,NULL TimeToResolve
,CASE
WHEN ReportSubStatus IN ('Duplicate?','DNA''d Last Appt','CAB patient Cancelled','Manual Patient Cancelled',
'Manual Referral Needs Booking','Physiotherapy Direct Access', 'Canx Last Appt',
'EMG Direct Access','Awaiting Triage') THEN 'Admin'
WHEN ReportStatus = 'Waiting List' THEN 'Admin'
WHEN ReportStatus = 'Previous Attended, No Future' AND NoOfPreviousTLT > 0 THEN 'Physio'
WHEN ReportSubStatus = 'Clinical Assessment Required' THEN 'Consultant'
END Responsibility
,CASE
WHEN ReportSubStatus = 'Duplicate?' THEN 'Clear Duplicates'
WHEN ReportSubStatus IN ('CAB patient Cancelled', 'Manual Patient Cancelled', 'Canx Last Appt') THEN 'Rebook/Cancel'
WHEN ReportSubStatus IN ('Manual Referral Needs Booking', 'EMG Direct Access') THEN 'Book Appts'
WHEN ReportSubStatus = 'Awaiting Triage' THEN 'Book Triage'
WHEN ReportSubStatus ='DNA''d Last Appt' THEN 'Manage DNAs'
WHEN ReportStatus = 'Waiting List' OR ReportSubStatus = 'Physiotherapy Direct Access' THEN 'Validate Waiting List'
WHEN ReportStatus = 'Previous Attended, No Future' AND NoOfPreviousTLT > 0 THEN 'Review Referral'
WHEN ReportSubStatus = 'Clinical Assessment Required' THEN 'Review Referral'
END ResponsibilityDetails
,BreachExpiryDate
,BreachActionReq
,BreachComments
,KPIClockStart
,FutureInvestigtionType
,TLTOrderedDate
,TLTApptType
,TLT14Days
,TLT42Days
,TLT126Days
,DaysDiff
,QuestionAnswer
,ActionExpiryDate
,ActionExpired
,ActionComments
,AnyActions
FROM
(
SELECT DISTINCT ReportStatus
,CASE
WHEN ReportStatus = 'Waiting List' THEN (
SELECT TOP 1 Investigation
FROM #Investigations
WHERE ReferralID = ReferralID)
WHEN ReportStatus = 'Previous Attended, No Future' THEN
CASE
WHEN LastApptDNA = 'Yes' AND (
SELECT TOP 1 a1.name
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE care_catsreferral_appointmen = ReferralID
AND SBA.lkp_apptstatus = -587
AND SBA.appointmen = (
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = ReferralID
AND SBA.appointmen < GETDATE()
ORDER BY SBA.appointmen DESC)
ORDER BY SBA.appointmen DESC) LIKE '%TLT%' THEN 'DNA''d Last Appt - TLT'
WHEN LastApptDNA = 'Yes' THEN 'DNA''d Last Appt - Not TLT'
WHEN LastApptCanx = 'Yes' THEN 'Canx Last Appt'
WHEN LastAttendedApptType LIKE '%TLT%' THEN 'Previous Appt was TLT'
ELSE 'Clinical Assessment Required'
END
WHEN ReportStatus = 'Nothing Attended, Nothing Booked' THEN
CASE
WHEN LastApptDNA = 'Yes' THEN 'DNA''d Last Appt'
WHEN LastApptCanx = 'Yes' AND IsCAB = 'Yes' THEN 'CAB Patient Cancelled'
WHEN LastApptCanx = 'Yes' then 'Manual Patient Cancelled'
WHEN ReceiptStatus <> 'Less Than 8 Weeks Old' THEN 'Duplicate?'
WHEN Specialty = 'Physiotherapy' THEN 'Physiotherapy Direct Access'
WHEN Specialty = 'EMG' THEN 'EMG Direct Access'
WHEN ReferralStatus IN ('Referral Accepted', 'Referral_Received') THEN 'Manual Referral Needs Booking'
ELSE 'Awaiting Triage'
END
WHEN ReportStatus LIKE 'Future%' THEN FutureApptType
END ReportSubStatus
,ReceiptStatus
,ReferralID
,NHSNumber
,IsCAB
,DateOfReferral
,WeekOfReferral
,WeeksWaitingToday
,ReferralStatus
,Specialty
,Consultant
,TLTName
,FirstAttendedApptDate
,FirstAttendedApptType
,FirstAttendedApptLocation
,LastAttendedApptDate
,LastAttendedApptType
,LastAttendedApptLocation
,LastApptDNA
,LastApptCanx
,FutureApptDate
,FutureApptType
,FutureApptLocation
,CASE
WHEN ReportStatus LIKE 'Future%' THEN
CASE
WHEN DATEDIFF(dd,GETDATE(),FutureApptDate) <= 14 THEN 'Less Than 2 Weeks'
WHEN DATEDIFF(dd,GETDATE(),FutureApptDate) <= 21 THEN '2 - 3 Weeks'
WHEN DATEDIFF(dd,GETDATE(),FutureApptDate) <= 28 THEN '3 - 4 Weeks'
ELSE 'More Than 4 Weeks'
END
END TimeUntilFutureAppt
,NoOfPreviousTLT
,NoOfFutureTLT
,NoOfFollowUps
,BreachExpiryDate
,BreachActionReq
,BreachComments
,KPIClockStart
,FutureInvestigtionType
,TLTOrderedDate
,TLTApptType
,TLT14Days
,TLT42Days
,TLT126Days
,DaysDiff
,QuestionAnswer
,ActionExpiryDate
,ActionExpired
,ActionComments
,AnyActions
FROM
(
SELECT DISTINCT cr.id ReferralID
,PI.c_val NHSNumber
,S.servicenam Specialty
,RLD.dateofrefe DateOfReferral
,CASE
WHEN cr.iscab = 0 THEN 'No'
ELSE 'Yes'
END IsCAB
,DATEADD("dd", 0 - DATEPART("dw", RLD.dateOfRefe) + 2,RLD.dateOfRefe) WeekOfReferral
,ROUND(CONVERT(FLOAT,(CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME) - rld.dateofrefe))/7,0,1) WeeksWaitingToday
,sl.text ReferralStatus
,(
SELECT TOP 1 name.nameforename + ' ' + name.namesurname Consultant
FROM care_catsreferral r
LEFT OUTER JOIN care_consultationde conde ON conde.catsreferr = r.id
LEFT OUTER JOIN care_consultationti conti ON conde.id = conti.care_consultationde_consultati
LEFT OUTER JOIN core_hcp hcp ON conti.starthcp = hcp.id
LEFT OUTER JOIN core_memberofstaff name ON hcp.id = name.hcp
WHERE r.id = cr.id
) Consultant
,(
SELECT TOP 1 name.nameforename + ' ' + name.namesurname Consultant
FROM care_catsreferral r
LEFT OUTER JOIN care_consultationde conde ON conde.catsreferr = r.id
LEFT OUTER JOIN care_tltcontacttime conti ON conde.id = conti.care_consultationde_tlttimes
LEFT OUTER JOIN core_hcp hcp ON conti.startthera = hcp.id
LEFT OUTER JOIN core_memberofstaff name ON hcp.id = name.hcp
WHERE r.id = cr.id
) TLTName
,CASE
WHEN RLD.dateofrefe < '04/jan/2010' THEN 'Backlog'
WHEN RLD.dateofrefe < DATEADD(ww,-8,GETDATE()) THEN 'More Than 8 Weeks Old'
ELSE 'Less Than 8 Weeks Old'
END ReceiptStatus
,CASE
WHEN (
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE care_catsreferral_appointmen = cr.id
AND sba.appointmen + sba.apptstartt >= GETDATE()
AND SBA.lkp_apptstatus <> -568
AND a1.name IS NOT NULL
ORDER BY SBA.appointmen
) IS NOT NULL AND
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus IN (-1407, -1408)
ORDER BY SBA.appointmen DESC
) IS NULL THEN 'Future Appt Booked, No Previous'
WHEN
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE care_catsreferral_appointmen = cr.id
AND sba.appointmen + sba.apptstartt >= GETDATE()
AND SBA.lkp_apptstatus <> -568
AND a1.name IS NOT NULL
ORDER BY SBA.appointmen
) IS NOT NULL THEN 'Future Appt Booked, Previous Attended'
WHEN cr.id IN (SELECT ReferralID FROM #Investigations) THEN 'Waiting List'
WHEN
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus IN (-1407, -1408)
ORDER BY SBA.appointmen DESC
) IS NOT NULL THEN 'Previous Attended, No Future'
ELSE 'Nothing Attended, Nothing Booked'
END ReportStatus
,#FirstAppts.ApptDate FirstAttendedApptDate
,#FirstAppts.ApptType FirstAttendedApptType
,#FirstAppts.ApptLocation FirstAttendedApptLocation
,#LastAppts.ApptDate LastAttendedApptDate
,#LastAppts.ApptType LastAttendedApptType
,#LastAppts.ApptLocation LastAttendedApptLocation
,#FutureAppts.ApptDate FutureApptDate
,#FutureAppts.ApptType FutureApptType
,#FutureAppts.ApptLocation FutureApptLocation
,CASE
WHEN
(
SELECT TOP 1 appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus = -587
AND appointmen =
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND appointmen < GETDATE()
ORDER BY SBA.appointmen DESC
)
ORDER BY appointmen DESC
) IS NULL THEN 'No'
ELSE 'Yes'
END LastApptDNA
,CASE
WHEN
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus = -568
AND SBA.appointmen =
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.appointmen < GETDATE()
ORDER BY SBA.appointmen DESC
)
ORDER BY appointmen DESC
) IS NULL THEN 'No'
ELSE 'Yes'
END LastApptCanx
,(
SELECT COUNT(cr1.id)
FROM care_catsreferral cr1
LEFT OUTER JOIN schl_booking_appoin a ON a.care_catsreferral_appointmen = cr1.id
LEFT OUTER JOIN schl_sess_slot ss ON a.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE a1.name LIKE '%TLT%'
AND a.lkp_apptstatus IN (-1407, -1408)
AND cr1.id = cr.id
) NoOfPreviousTLT
,(
SELECT COUNT(cr1.id)
FROM care_catsreferral cr1
LEFT OUTER JOIN schl_booking_appoin a ON a.care_catsreferral_appointmen = cr1.id
LEFT OUTER JOIN schl_sess_slot ss ON a.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE a1.name LIKE '%TLT%'
and a.appointmen + a.apptstartt >= GETDATE()
AND cr1.id = cr.id
) NoOfFutureTLT
,(
SELECT COUNT(cr1.id)
FROM care_catsreferral cr1
LEFT OUTER JOIN schl_booking_appoin a ON a.care_catsreferral_appointmen = cr1.id
LEFT OUTER JOIN schl_sess_slot ss ON a.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE CHARINDEX('Follow-Up',a1.name) > 0
AND a.lkp_apptstatus IN (-1407, -1408)
AND cr1.id = cr.id
) NoOfFollowUps
,#BreachUA.BreachExpiryDate
,#BreachUA.BreachActionReq
,#BreachUA.BreachComments
,cr.triagedate KPIClockStart
,CASE
WHEN #FutureAppts.ApptType = 'Investigation' THEN #FutureInvestigations.InvestApptType
END FutureInvestigtionType
,#TLTBookings.TLTOrderedDate
,CASE
WHEN #TLTBookingsAppts.Specialty IS NULL THEN #TLTBookings.TLTApptType
ELSE #TLTBookingsAppts.Specialty
END TLTApptType
,#TLTBookings.TLT14Days
,#TLTBookings.TLT42Days
,#TLTBookings.TLT126Days
,#TLTBookings.DaysDiff
,#UserAssesment.QuestionAnswer
,#UserAssesment.ActionExpiryDate
,#UserAssesment.ActionExpired
,#UserAssesment.ActionComments
,#UserAssesment.AnyActions
FROM care_catsreferral cr
LEFT OUTER JOIN core_patient P ON cr.patient = P.id
LEFT OUTER JOIN core_referralletter RLD ON cr.referralde = RLD.id
LEFT OUTER JOIN core_patient_c_identifi PI ON P.id = PI.id
LEFT OUTER JOIN core_services S ON RLD.service = S.id
LEFT OUTER JOIN care_providercancel canx ON cr.providerca = canx.id
LEFT OUTER JOIN applookup_instance canx1 ON canx.lkp_reason = canx1.id
LEFT OUTER JOIN care_catsreferralst rs ON cr.currentsta = rs.id
LEFT OUTER JOIN applookup_instance sl ON rs.lkp_referralst = sl.id
LEFT OUTER JOIN #FirstAppts ON #FirstAppts.ReferralID = cr.id
LEFT OUTER JOIN #LastAppts ON #LastAppts.ReferralID = cr.id
LEFT OUTER JOIN #FutureAppts ON #FutureAppts.ReferralID = cr.id
LEFT OUTER JOIN #BreachUA ON #BreachUA.ReferralID = cr.id
LEFT OUTER JOIN #Investigations ON #Investigations.ReferralID = cr.id
LEFT OUTER JOIN #FutureInvestigations ON #FutureInvestigations.ReferralID = cr.id
LEFT OUTER JOIN #TLTBookings ON #TLTBookings.ReferralID = cr.id
LEFT OUTER JOIN #TLTBookingsAppts ON #TLTBookingsAppts.ReferralID = cr.id
LEFT OUTER JOIN #UserAssesment ON #UserAssesment.ReferralID = cr.id
WHERE cr.discharged IS NULL
AND cr.providerCa IS NULL
AND cr.rejectRefe IS NULL
AND PI.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116',
'7048628863', '9111111194', '7048481595', '4111111149', '9990019924',
'3111111113', '4111111114', '5111111115', '6111111116', '7111111117',
'4523655987', '8777777778')
AND S.serviceNam NOT LIKE '%Ophthalmology%'
AND S.servicenam NOT LIKE '%Lincs%'
AND cr.id = 89759
) TEMP
) TEMP
ORDER BY DateOfReferral
--**************************
DROP TABLE #TLTBookingsAppts
DROP TABLE #TLTBookings
DROP TABLE #Investigations
DROP TABLE #FutureInvestigations
DROP TABLE #FutureAppts
DROP TABLE #LastAppts
DROP TABLE #FirstAppts
DROP TABLE #BreachUA
DROP TABLE #UserAssesment
DROP TABLE #UA
DROP TABLE #SequenceA
DROP TABLE #SequenceB
DROP TABLE #SequenceC
DROP TABLE #SequenceD
August 2, 2011 at 9:33 am
Too big to optimize on a web site.
Give or take a little bit of luck this should take anywhere between 8 to 40 hours to tune (depending on how fast you need it to go and how much work needs to be done).
Whatever you do don't use the DTA for this, it usually hurts more than anything else if you don't know how to choose the correct indexes to add.
What you can do for free help is run that proc on a server with profiler turned on with the default trace and then adding those events :
-performance / showplan XML statistics profile
-stored proc / StmtCompleted
Then post back when you know what step(s) are taking way too long. Then maybe we can offer a few pointers.
August 2, 2011 at 9:35 am
WOW!!!! There is no way anybody is going to decipher that for the wages we make around here.
There is a ton of stuff in there that is crippling your performance by making your query non-SARGEable. You have tons of functions in where clauses. You have sub selects nested inside case statements. You have table scan after table scan. It would take days to make that perform better. I even saw a select distinct from select distinct from select distinct.
I don't mean to so negative but that would seriously take a week or more to restructure and test.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2011 at 9:36 am
How long does it take to run if you skip report status?
August 2, 2011 at 9:38 am
well first that's not just one query...i count 15 queries, the one at the end is pretty huge.
maybe you could pull a specific query out that is giving you an a specific performance issue out of the 802 lines of code you posted...we are all volunteers here, and that much code to reviews beyond what i'd typically throw some time at to help out with.
you posted queries, but nothing i could copy and paste into SSMS to test with...i don't have your tables, so other than a syntax check, i could look at much more than that.
some little things i saw: the first 4 queries are identical, except for a change on a signle column in the where statement...are you doing anything with those 4 queries? why are they included?
could they be in the same dataset?
on the big one at the end:
there is a lot of NOT IN('9990141444', '... statements.
that's a performance killer, because a NOT in requires a scan of all the data, where the opposite, an IN(somesetofdata) can possibly use a seek.
same thing for the NOT LIKE stuff
AND S.serviceNam NOT LIKE '%Ophthalmology%'
AND S.servicenam NOT LIKE '%Lincs%'
you would probably benefit from a divide and conquer teechnique.
instead of doing NOT LIKE...use a CTE to get the subset of data you DO want to use...then join agaisnt it to ge tthe results.
actual execution plan is the gold standard for perfromance analysis. Ideally, that's what we need, but waiting 40+ minutes is a lost cause.
at a minimum, for the bad performaning query, show us the estimated exection plan....maybe we can tune it enough to get it to where you don't have to wait 40 minutes,a nd we can look at it closer fromt here.
Lowell
August 2, 2011 at 9:46 am
Try this:
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,CASE
WHEN a7.c_sequen IN ( 0 , 3 , 4 ) THEN c13.optiontext
END AnswerOption
,CASE
WHEN a7.c_sequen = 1 THEN a16.dateanswer
END ExpiryDate
INTO #SequenceAll
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen IN ( 0 , 1 ,3 , 4 )
--**************************
/*
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,a16.dateanswer ExpiryDate
INTO #SequenceB
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 1
--**************************
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,c13.optiontext AnswerOption
INTO #SequenceC
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 3
--**************************
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,c7.c_te QuestionInForm
,c13.optiontext AnswerOption
INTO #SequenceD
FROM care_catsreferral c1
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 4
*/
--**************************
SELECT cr.id ReferralID
,a5.id UAID
,a10.authoringiauthoringd AuthDate
,c6.nameforename+' '+c6.namesurname AuthName
,A.AnswerOption Undischarged
,A.ExpiryDate
,CASE
WHEN A.ExpiryDate IS NULL THEN NULL
WHEN DATEDIFF(dd,CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME), A.ExpiryDate) < 0 THEN 'Yes'
ELSE 'No'
END ActionExpired
,A.AnswerOption Actions
,A.AnswerOption MSKResultsForReview
,CASE
WHEN A.AnswerOption != 'Consultant Action Required' THEN 'Undischarged - ' + + A.AnswerOption
WHEN A.AnswerOption = 'Consultant Action Required' THEN 'MSK Results Review - ' + + A.AnswerOption
END QuestionAnswer
INTO #UA
FROM care_catsreferral cr
LEFT OUTER JOIN asse_patientassessi a5 ON cr.carecontex = a5.carecontex
LEFT OUTER JOIN asse_patientassess2 a6 ON a5.assessment = a6.id
LEFT OUTER JOIN asse_userassessment a7 ON a6.userassess = a7.id
LEFT OUTER JOIN asse_patientassess3 a8 ON a6.id = a8.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a9 ON a8.id = a9.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a10 ON a9.id = a10.asse_assessmentpatq_patientans
LEFT OUTER JOIN core_hcp c5 ON a10.authoringiauthoringh = c5.id
LEFT OUTER JOIN core_memberofstaff c6 ON c5.id = c6.hcp
LEFT OUTER JOIN core_patient c12 ON cr.patient = c12.id
LEFT OUTER JOIN core_patient_c_identifi c13 ON c12.id = c13.id
LEFT OUTER JOIN #SequenceAll A ON a5.id = A.UAID
--LEFT OUTER JOIN #SequenceB B ON a5.id = B.UAID
--LEFT OUTER JOIN #SequenceC C ON a5.id = C.UAID
--LEFT OUTER JOIN #SequenceD D ON a5.id = D.UAID
WHERE a10.authoringiauthoringd IS NOT NULL
AND a7.name LIKE '%Undischarged%'
AND c13.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116', '9111111194',
'7048481595', '4111111149', '9990019924', '3111111113', '4111111114', '5111111115',
'6111111116', '7111111117', '8777777778', '4523653987','9990377952')
AND ( A.AnswerOption IS NOT NULL
OR A.ExpiryDate IS NOT NULL
OR A.QuestionInForm IS NOT NULL
)
--**************************
SELECT DISTINCT c1.id AS ReferralID
,MAX(UA.UAID) OVER(PARTITION BY c1.id) UAID
,MAX(UA.AuthDate) OVER(PARTITION BY c1.id) AuthDate
,MAX(UA.QuestionAnswer) OVER(PARTITION BY c1.id) QuestionAnswer
,MAX(UA.ExpiryDate) OVER(PARTITION BY c1.id) ActionExpiryDate
,CASE
WHEN MAX(UA.ExpiryDate) OVER(PARTITION BY c1.id) IS NULL THEN NULL
WHEN DATEDIFF(dd,CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME), MAX(UA.ExpiryDate) OVER(PARTITION BY c1.id)) < 0 THEN 'Yes'
ELSE 'No'
END ActionExpired
,(
SELECT TOP 1 REPLACE(REPLACE(CONVERT(VARCHAR(2560),a16.stringansw), CHAR(10),' '), CHAR(13),' ') Comments
FROM care_catsreferral c11
LEFT OUTER JOIN asse_patientassessi a1 ON c11.carecontex = a1.carecontex
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id
LEFT OUTER JOIN asse_userassessment a3 ON a2.userassess = a3.id
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta
LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id
LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id
WHERE a3.name = 'Undischarged'
AND a7.c_sequen = 2
AND c11.id = c1.id
ORDER BY a1.id DESC
) ActionComments
,MAX(UA.Actions) OVER(PARTITION BY c1.id) AnyActions
INTO #UserAssesment
FROM care_catsreferral c1
LEFT OUTER JOIN core_patient c2 ON c1.patient = c2.id
LEFT OUTER JOIN core_patient_c_identifi c3 ON c2.id = c3.id
LEFT OUTER JOIN #UA UA ON c1.id = UA.ReferralID
WHERE c3.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116', '9111111194', '7048481595',
'4111111149', '9990019924', '3111111113', '4111111114', '5111111115', '6111111116', '7111111117',
'8777777778', '4523653987','9990377952')
AND UA.UAID IS NOT NULL
--**************************
SELECT ReferralID
,MAX(ExpiryDate) BreachExpiryDate
,MAX(ActionRequired) BreachActionReq
,MAX(Comments) BreachComments
INTO #BreachUA
FROM
(
SELECT c1.id ReferralID
,a12.dateanswer ExpiryDate
,c6.optiontext ActionRequired
,a12.stringansw Comments
,a11.authoringiauthoringd EntryDate
,MAX(a11.authoringiauthoringd)
OVER(PARTITION BY c1.id) LastEntry
FROM asse_patientassessi a2
LEFT OUTER JOIN care_catsreferral c1 ON a2.carecontex = c1.carecontex
LEFT OUTER JOIN asse_patientassess2 a3 ON a2.assessment = a3.id
LEFT OUTER JOIN asse_userassessment a4 ON a3.userassess = a4.id
LEFT OUTER JOIN asse_patientassess3 a5 ON a3.id = a5.asse_patientassess2_answergrou
LEFT OUTER JOIN asse_assessmentpatq a7 ON a5.id = a7.asse_patientassess3_assessmen2
LEFT OUTER JOIN asse_patientassess4 a11 ON a7.id = a11.asse_assessmentpatq_patientans
LEFT OUTER JOIN asse_patientanswerd a12 ON a11.id = a12.asse_patientassess4_answerdeta
LEFT OUTER JOIN asse_patientanswerd_multiselec a15 ON a12.id = a15.from_side
LEFT OUTER JOIN core_answeroption c6 ON a15.to_side = c6.id
WHERE a4.name LIKE '%Advised Breach of 56 Day KPI%'
) TEMP
WHERE EntryDate = LastEntry
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MIN(FirstApptDate) ApptDate
,MIN(FirstApptType) ApptType
,MIN(FirstApptLocation) ApptLocation
INTO #FirstAppts
FROM
(
SELECT care_catsreferral_appointmen ReferralID
,SBA.appointmen FirstApptDate
,a1.name FirstApptType
,l.name FirstApptLocation
,MIN(SBA.appointmen) OVER(PARTITION BY SBA.care_catsreferral_appointmen) FirstRefAppt
FROM schl_booking_appoin AS SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
LEFT OUTER JOIN schl_sch_session sss ON SBA.c_sessi = sss.id
LEFT OUTER JOIN core_location l ON sss.schlocatio = l.id
WHERE SBA.lkp_apptstatus IN (-1407, -1408)
) TEMP
WHERE FirstApptDate = FirstRefAppt
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MAX(FirstApptDate) ApptDate
,MAX(FirstApptType) ApptType
,MAX(FirstApptLocation) ApptLocation
INTO #LastAppts
FROM
(
SELECT care_catsreferral_appointmen ReferralID
,SBA.appointmen FirstApptDate
,a1.name FirstApptType
,l.name FirstApptLocation
,MAX(SBA.appointmen) OVER(PARTITION BY SBA.care_catsreferral_appointmen) FirstRefAppt
FROM schl_booking_appoin AS SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
LEFT OUTER JOIN schl_sch_session sss ON SBA.c_sessi = sss.id
LEFT OUTER JOIN core_location l ON sss.schlocatio = l.id
WHERE SBA.lkp_apptstatus IN (-1407, -1408)
) TEMP
WHERE FirstApptDate = FirstRefAppt
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MIN(FirstApptDate) ApptDate
,MIN(FirstApptType) ApptType
,MIN(FirstApptLocation) ApptLocation
INTO #FutureAppts
FROM
(
SELECT care_catsreferral_appointmen ReferralID
,SBA.appointmen FirstApptDate
,SBA.apptstartt FirstApptTime
,a1.name FirstApptType
,l.name FirstApptLocation
,MIN(SBA.appointmen + sba.apptstartt) OVER(PARTITION BY SBA.care_catsreferral_appointmen) FirstRefAppt
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sch_session sss ON SBA.c_sessi = sss.id
LEFT OUTER JOIN core_location l ON sss.schlocatio = l.id
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE SBA.appointmen + sba.apptstartt >= GETDATE()
AND SBA.lkp_apptstatus <> -568
AND a1.name IS NOT NULL
) TEMP
WHERE FirstApptDate + FirstApptTime = FirstRefAppt
GROUP BY ReferralID
--**************************
SELECT ReferralID
,MIN(ApptDate) InvestApptDate
,MIN(ServiceName) InvestApptType
INTO #FutureInvestigations
FROM
(
SELECT DISTINCT c1.id ReferralID
,s1.appointmen ApptDate
,s1.apptstartt ApptTime
,c5.servicenam ServiceName
,MIN(s1.appointmen + s1.apptstartt) OVER(PARTITION BY c1.id) FirstAppt
FROM care_catsreferral c1
LEFT OUTER JOIN schl_booking_appoin s1 ON c1.id = s1.care_catsreferral_appointmen
LEFT OUTER JOIN ocrr_ocsorder o1 ON c1.id = o1.care_catsreferral_investigat
LEFT OUTER JOIN schl_sch_session s2 ON s1.c_sessi = s2.id
LEFT OUTER JOIN schl_sess_slot s3 ON s1.id = s3.appointmen
LEFT OUTER JOIN applookup_instance a1 ON s1.lkp_apptstatus = a1.id
LEFT OUTER JOIN core_location c2 ON s2.schlocatio = c2.id
LEFT OUTER JOIN core_activity c3 ON s3.activity = c3.id
LEFT OUTER JOIN ocrr_orderinvestiga o2 ON o1.id = o2.orderdetai
LEFT OUTER JOIN ocrr_investigation o3 ON o2.investigat = o3.id
LEFT OUTER JOIN ocrr_locsvcprovsys o4 ON o3.providerse = o4.id
LEFT OUTER JOIN core_locationservic c4 ON o4.locationse = c4.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
WHERE s1.appointmen + s1.apptstartt >= GETDATE()
AND c3.name LIKE '%Investig%'
AND s1.lkp_apptstatus <> -568
AND c5.servicenam NOT IN ('physiotherapy', 'Podiatry')
) TEMP
WHERE ApptDate + ApptTime = FirstAppt
GROUP BY ReferralID
--**************************
SELECT DISTINCT c1.id ReferralID
,CASE
WHEN c5.servicenam IS NULL THEN o5.name
ELSE c5.servicenam
END Investigation
INTO #Investigations
FROM care_catsreferral c1
LEFT OUTER JOIN core_patient c2 ON c1.patient = c2.id
LEFT OUTER JOIN ocrr_ocsorder o1 ON c1.id = o1.care_catsreferral_investigat
LEFT OUTER JOIN core_patient_c_identifi c3 ON c2.id = c3.id
LEFT OUTER JOIN ocrr_orderinvestiga o2 ON o1.id = o2.orderdetai
LEFT OUTER JOIN ocrr_investigation o3 ON o2.investigat = o3.id
LEFT OUTER JOIN ocrr_orderinvestiga_ordinvstat o6 ON o2.id = o6.id
LEFT OUTER JOIN applookup_instance a5 ON o2.lkp_ordinvcurrordinvstat = a5.id
LEFT OUTER JOIN ocrr_locsvcprovsys o4 ON o3.providerse = o4.id
LEFT OUTER JOIN ocrr_investigationi o5 ON o3.investigat = o5.id
LEFT OUTER JOIN core_locationservic c4 ON o4.locationse = c4.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
LEFT OUTER JOIN applookup_instance a1 ON o5.lkp_category = a1.id
LEFT OUTER JOIN applookup_instance a2 ON o6.lkp_ordinvstat = a2.id
LEFT OUTER JOIN applookup_instance a3 ON o6.lkp_statuschan = a3.id
WHERE a1.[text] <> 'Pathology'
AND a5.[text] NOT IN ('Cancel Request', 'Cancelled')
AND o2.appointmen IS NULL
AND c3.c_val NOT IN ('3111111113', '9990019924', '4111111114', '5111111115',
'6111111116', '7111111117', '7048481595', '3000000003',
'6188490774', '4786214116', '7048628863', '9111111194',
'4523655987')
--**************************
SELECT DISTINCT c1.id AS ReferralID
,MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id) TLTOrderedDate
,DATEADD(dd, 14, MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id)) TLT14Days
,DATEADD(dd, 42, MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id)) TLT42Days
,DATEADD(dd, 126, MIN(o1.ordinvcurrchangedate) OVER(PARTITION BY c1.id)) TLT126Days
,DATEDIFF(ww,MIN(c1.triagedate) OVER(PARTITION BY c1.id),GETDATE()) DaysDiff
,MIN(c5.servicenam) OVER(PARTITION BY c1.id) TLTApptType
INTO #TLTBookings
FROM care_catsreferral c1
LEFT OUTER JOIN care_orderinvappt c2 ON c1.id = c2.care_catsreferral_orderinvap
LEFT OUTER JOIN core_patient c6 ON c1.patient = c6.id
LEFT OUTER JOIN ocrr_orderinvestiga o1 ON c2.orderinves = o1.id
LEFT OUTER JOIN schl_booking_appoin s1 ON c2.appointmen = s1.id
LEFT OUTER JOIN ocrr_investigation o2 ON o1.investigat = o2.id
LEFT OUTER JOIN applookup_instance a4 ON o1.lkp_ordinvcurrordinvstat = a4.id
LEFT OUTER JOIN ocrr_investigationi o3 ON o2.investigat = o3.id
LEFT OUTER JOIN ocrr_locsvcprovsys o5 ON o2.providerse = o5.id
LEFT OUTER JOIN core_activity c3 ON o3.activity = c3.id
LEFT OUTER JOIN core_locationservic c4 ON o5.locationse = c4.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
LEFT OUTER JOIN schl_appt_history_s s2 ON s1.currentsta = s2.id
LEFT OUTER JOIN applookup_instance a1 ON s2.lkp_status = a1.id
LEFT OUTER JOIN core_patient_c_identifi c7 ON c6.id = c7.id
WHERE (c3.name LIKE '%Podiatry%'
OR c3.name LIKE '%Physio%')
AND (a1.text IN ('Booked', 'Seen')
OR a1.text is null)
AND a4.text NOT LIKE '%Cancel%'
AND c7.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116',
'7048628863', '9111111194', '7048481595', '4111111149', '9990019924',
'3111111113', '4111111114', '5111111115', '6111111116', '7111111117',
'4523655987')
--**************************
SELECT DISTINCT c1.id ReferralID
,c7.servicenam Specialty
,c6.name Activity
,s1.appointmen ApptDate
INTO #TLTBookingsAppts
FROM care_catsreferral c1
LEFT OUTER JOIN core_patient c2 ON c1.patient = c2.id
LEFT OUTER JOIN schl_booking_appoin s1 ON c1.id = s1.care_catsreferral_appointmen
LEFT OUTER JOIN core_referralletter c4 ON c1.referralde = c4.id
LEFT OUTER JOIN core_patient_c_identifi c3 ON c2.id = c3.id
LEFT OUTER JOIN schl_sess_slot s2 ON s1.id = s2.appointmen
LEFT OUTER JOIN applookup_instance a1 ON s1.lkp_apptstatus = a1.id
LEFT OUTER JOIN core_activity c6 ON s2.activity = c6.id
LEFT OUTER JOIN schl_sch_session s3 ON s2.c_sessi = s3.id
LEFT OUTER JOIN core_services c7 ON s3.service = c7.id
LEFT OUTER JOIN core_location c8 ON s3.schlocatio = c8.id
LEFT OUTER JOIN core_services c5 ON c4.service = c5.id
WHERE a1.text LIKE '%Booked%'
AND (s3.name LIKE '%Physio%'
OR s3.name LIKE '%Podiatry%')
AND c3.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774',
'4786214116', '7048628863', '9111111194', '7048481595',
'4111111149', '9990019924', '3111111113', '4111111114',
'5111111115', '6111111116', '7111111117', '4523655987')
--**************************
SELECT DISTINCT
CASE
WHEN ReportSubStatus = 'Physiotherapy Direct Access' THEN 'Waiting List'
ELSE ReportStatus
END ReportStatus
,ReportSubStatus
,ReceiptStatus
,ReferralID
,NHSNumber
,IsCAB
,DateOfReferral
,WeekOfReferral
,CASE
WHEN ReceiptStatus = 'Backlog' THEN 'Backlog'
ELSE
CASE
WHEN LEN(DATEPART(mm,DateOfReferral)) = 1 THEN '0' + CONVERT(CHAR(1),DATEPART(mm,DateOfReferral))
+ '/' + CONVERT(CHAR(4),DATEPART(yyyy,DateOfReferral))
ELSE CONVERT(CHAR(2),DATEPART(mm,DateOfReferral)) + '/' + CONVERT(CHAR(4),DATEPART(yyyy,DateOfReferral))
END
END MonthOfReferral
,WeeksWaitingToday
,ReferralStatus
,Specialty
,Consultant
,TLTName
,FirstAttendedApptDate
,FirstAttendedApptType
,FirstAttendedApptLocation
,LastAttendedApptDate
,LastAttendedApptType
,LastAttendedApptLocation
,FutureApptDate
,FutureApptType
,FutureApptLocation
,TimeUntilFutureAppt
,LastApptDNA
,LastApptCanx
,NoOfPreviousTLT
,NoOfFutureTLT
,NoOfFollowUps
,NULL TimeToResolve
,CASE
WHEN ReportSubStatus IN ('Duplicate?','DNA''d Last Appt','CAB patient Cancelled','Manual Patient Cancelled',
'Manual Referral Needs Booking','Physiotherapy Direct Access', 'Canx Last Appt',
'EMG Direct Access','Awaiting Triage') THEN 'Admin'
WHEN ReportStatus = 'Waiting List' THEN 'Admin'
WHEN ReportStatus = 'Previous Attended, No Future' AND NoOfPreviousTLT > 0 THEN 'Physio'
WHEN ReportSubStatus = 'Clinical Assessment Required' THEN 'Consultant'
END Responsibility
,CASE
WHEN ReportSubStatus = 'Duplicate?' THEN 'Clear Duplicates'
WHEN ReportSubStatus IN ('CAB patient Cancelled', 'Manual Patient Cancelled', 'Canx Last Appt') THEN 'Rebook/Cancel'
WHEN ReportSubStatus IN ('Manual Referral Needs Booking', 'EMG Direct Access') THEN 'Book Appts'
WHEN ReportSubStatus = 'Awaiting Triage' THEN 'Book Triage'
WHEN ReportSubStatus = 'DNA''d Last Appt' THEN 'Manage DNAs'
WHEN ReportStatus = 'Waiting List' OR ReportSubStatus = 'Physiotherapy Direct Access' THEN 'Validate Waiting List'
WHEN ReportStatus = 'Previous Attended, No Future' AND NoOfPreviousTLT > 0 THEN 'Review Referral'
WHEN ReportSubStatus = 'Clinical Assessment Required' THEN 'Review Referral'
END ResponsibilityDetails
,BreachExpiryDate
,BreachActionReq
,BreachComments
,KPIClockStart
,FutureInvestigtionType
,TLTOrderedDate
,TLTApptType
,TLT14Days
,TLT42Days
,TLT126Days
,DaysDiff
,QuestionAnswer
,ActionExpiryDate
,ActionExpired
,ActionComments
,AnyActions
FROM
(
SELECT DISTINCT ReportStatus
,CASE
WHEN ReportStatus = 'Waiting List' THEN (
SELECT TOP 1 Investigation
FROM #Investigations
WHERE ReferralID = ReferralID)
WHEN ReportStatus = 'Previous Attended, No Future' THEN
CASE
WHEN LastApptDNA = 'Yes' AND (
SELECT TOP 1 a1.name
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE care_catsreferral_appointmen = ReferralID
AND SBA.lkp_apptstatus = -587
AND SBA.appointmen = (
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = ReferralID
AND SBA.appointmen < GETDATE()
ORDER BY SBA.appointmen DESC)
ORDER BY SBA.appointmen DESC) LIKE '%TLT%' THEN 'DNA''d Last Appt - TLT'
WHEN LastApptDNA = 'Yes' THEN 'DNA''d Last Appt - Not TLT'
WHEN LastApptCanx = 'Yes' THEN 'Canx Last Appt'
WHEN LastAttendedApptType LIKE '%TLT%' THEN 'Previous Appt was TLT'
ELSE 'Clinical Assessment Required'
END
WHEN ReportStatus = 'Nothing Attended, Nothing Booked' THEN
CASE
WHEN LastApptDNA = 'Yes' THEN 'DNA''d Last Appt'
WHEN LastApptCanx = 'Yes' AND IsCAB = 'Yes' THEN 'CAB Patient Cancelled'
WHEN LastApptCanx = 'Yes' then 'Manual Patient Cancelled'
WHEN ReceiptStatus <> 'Less Than 8 Weeks Old' THEN 'Duplicate?'
WHEN Specialty = 'Physiotherapy' THEN 'Physiotherapy Direct Access'
WHEN Specialty = 'EMG' THEN 'EMG Direct Access'
WHEN ReferralStatus IN ('Referral Accepted', 'Referral_Received') THEN 'Manual Referral Needs Booking'
ELSE 'Awaiting Triage'
END
WHEN ReportStatus LIKE 'Future%' THEN FutureApptType
END ReportSubStatus
,ReceiptStatus
,ReferralID
,NHSNumber
,IsCAB
,DateOfReferral
,WeekOfReferral
,WeeksWaitingToday
,ReferralStatus
,Specialty
,Consultant
,TLTName
,FirstAttendedApptDate
,FirstAttendedApptType
,FirstAttendedApptLocation
,LastAttendedApptDate
,LastAttendedApptType
,LastAttendedApptLocation
,LastApptDNA
,LastApptCanx
,FutureApptDate
,FutureApptType
,FutureApptLocation
,CASE
WHEN ReportStatus LIKE 'Future%' THEN
CASE
WHEN DATEDIFF(dd,GETDATE(),FutureApptDate) <= 14 THEN 'Less Than 2 Weeks'
WHEN DATEDIFF(dd,GETDATE(),FutureApptDate) <= 21 THEN '2 - 3 Weeks'
WHEN DATEDIFF(dd,GETDATE(),FutureApptDate) <= 28 THEN '3 - 4 Weeks'
ELSE 'More Than 4 Weeks'
END
END TimeUntilFutureAppt
,NoOfPreviousTLT
,NoOfFutureTLT
,NoOfFollowUps
,BreachExpiryDate
,BreachActionReq
,BreachComments
,KPIClockStart
,FutureInvestigtionType
,TLTOrderedDate
,TLTApptType
,TLT14Days
,TLT42Days
,TLT126Days
,DaysDiff
,QuestionAnswer
,ActionExpiryDate
,ActionExpired
,ActionComments
,AnyActions
FROM
(
SELECT DISTINCT cr.id ReferralID
,PI.c_val NHSNumber
,S.servicenam Specialty
,RLD.dateofrefe DateOfReferral
,CASE
WHEN cr.iscab = 0 THEN 'No'
ELSE 'Yes'
END IsCAB
,DATEADD("dd", 0 - DATEPART("dw", RLD.dateOfRefe) + 2,RLD.dateOfRefe) WeekOfReferral
,ROUND(CONVERT(FLOAT,(CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME) - rld.dateofrefe))/7,0,1) WeeksWaitingToday
,sl.text ReferralStatus
,(
SELECT TOP 1 name.nameforename + ' ' + name.namesurname Consultant
FROM care_catsreferral r
LEFT OUTER JOIN care_consultationde conde ON conde.catsreferr = r.id
LEFT OUTER JOIN care_consultationti conti ON conde.id = conti.care_consultationde_consultati
LEFT OUTER JOIN core_hcp hcp ON conti.starthcp = hcp.id
LEFT OUTER JOIN core_memberofstaff name ON hcp.id = name.hcp
WHERE r.id = cr.id
) Consultant
,(
SELECT TOP 1 name.nameforename + ' ' + name.namesurname Consultant
FROM care_catsreferral r
LEFT OUTER JOIN care_consultationde conde ON conde.catsreferr = r.id
LEFT OUTER JOIN care_tltcontacttime conti ON conde.id = conti.care_consultationde_tlttimes
LEFT OUTER JOIN core_hcp hcp ON conti.startthera = hcp.id
LEFT OUTER JOIN core_memberofstaff name ON hcp.id = name.hcp
WHERE r.id = cr.id
) TLTName
,CASE
WHEN RLD.dateofrefe < '04/jan/2010' THEN 'Backlog'
WHEN RLD.dateofrefe < DATEADD(ww,-8,GETDATE()) THEN 'More Than 8 Weeks Old'
ELSE 'Less Than 8 Weeks Old'
END ReceiptStatus
,CASE
WHEN (
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE care_catsreferral_appointmen = cr.id
AND sba.appointmen + sba.apptstartt >= GETDATE()
AND SBA.lkp_apptstatus <> -568
AND a1.name IS NOT NULL
ORDER BY SBA.appointmen
) IS NOT NULL AND
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus IN (-1407, -1408)
ORDER BY SBA.appointmen DESC
) IS NULL THEN 'Future Appt Booked, No Previous'
WHEN
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE care_catsreferral_appointmen = cr.id
AND sba.appointmen + sba.apptstartt >= GETDATE()
AND SBA.lkp_apptstatus <> -568
AND a1.name IS NOT NULL
ORDER BY SBA.appointmen
) IS NOT NULL THEN 'Future Appt Booked, Previous Attended'
WHEN cr.id IN (SELECT ReferralID FROM #Investigations) THEN 'Waiting List'
WHEN
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus IN (-1407, -1408)
ORDER BY SBA.appointmen DESC
) IS NOT NULL THEN 'Previous Attended, No Future'
ELSE 'Nothing Attended, Nothing Booked'
END ReportStatus
,#FirstAppts.ApptDate FirstAttendedApptDate
,#FirstAppts.ApptType FirstAttendedApptType
,#FirstAppts.ApptLocation FirstAttendedApptLocation
,#LastAppts.ApptDate LastAttendedApptDate
,#LastAppts.ApptType LastAttendedApptType
,#LastAppts.ApptLocation LastAttendedApptLocation
,#FutureAppts.ApptDate FutureApptDate
,#FutureAppts.ApptType FutureApptType
,#FutureAppts.ApptLocation FutureApptLocation
,CASE
WHEN
(
SELECT TOP 1 appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus = -587
AND appointmen =
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND appointmen < GETDATE()
ORDER BY SBA.appointmen DESC
)
ORDER BY appointmen DESC
) IS NULL THEN 'No'
ELSE 'Yes'
END LastApptDNA
,CASE
WHEN
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.lkp_apptstatus = -568
AND SBA.appointmen =
(
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
WHERE care_catsreferral_appointmen = cr.id
AND SBA.appointmen < GETDATE()
ORDER BY SBA.appointmen DESC
)
ORDER BY appointmen DESC
) IS NULL THEN 'No'
ELSE 'Yes'
END LastApptCanx
,(
SELECT COUNT(cr1.id)
FROM care_catsreferral cr1
LEFT OUTER JOIN schl_booking_appoin a ON a.care_catsreferral_appointmen = cr1.id
LEFT OUTER JOIN schl_sess_slot ss ON a.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE a1.name LIKE '%TLT%'
AND a.lkp_apptstatus IN (-1407, -1408)
AND cr1.id = cr.id
) NoOfPreviousTLT
,(
SELECT COUNT(cr1.id)
FROM care_catsreferral cr1
LEFT OUTER JOIN schl_booking_appoin a ON a.care_catsreferral_appointmen = cr1.id
LEFT OUTER JOIN schl_sess_slot ss ON a.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE a1.name LIKE '%TLT%'
and a.appointmen + a.apptstartt >= GETDATE()
AND cr1.id = cr.id
) NoOfFutureTLT
,(
SELECT COUNT(cr1.id)
FROM care_catsreferral cr1
LEFT OUTER JOIN schl_booking_appoin a ON a.care_catsreferral_appointmen = cr1.id
LEFT OUTER JOIN schl_sess_slot ss ON a.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE CHARINDEX('Follow-Up',a1.name) > 0
AND a.lkp_apptstatus IN (-1407, -1408)
AND cr1.id = cr.id
) NoOfFollowUps
,#BreachUA.BreachExpiryDate
,#BreachUA.BreachActionReq
,#BreachUA.BreachComments
,cr.triagedate KPIClockStart
,CASE
WHEN #FutureAppts.ApptType = 'Investigation' THEN #FutureInvestigations.InvestApptType
END FutureInvestigtionType
,#TLTBookings.TLTOrderedDate
,CASE
WHEN #TLTBookingsAppts.Specialty IS NULL THEN #TLTBookings.TLTApptType
ELSE #TLTBookingsAppts.Specialty
END TLTApptType
,#TLTBookings.TLT14Days
,#TLTBookings.TLT42Days
,#TLTBookings.TLT126Days
,#TLTBookings.DaysDiff
,#UserAssesment.QuestionAnswer
,#UserAssesment.ActionExpiryDate
,#UserAssesment.ActionExpired
,#UserAssesment.ActionComments
,#UserAssesment.AnyActions
FROM care_catsreferral cr
LEFT OUTER JOIN core_patient P ON cr.patient = P.id
LEFT OUTER JOIN core_referralletter RLD ON cr.referralde = RLD.id
LEFT OUTER JOIN core_patient_c_identifi PI ON P.id = PI.id
LEFT OUTER JOIN core_services S ON RLD.service = S.id
LEFT OUTER JOIN care_providercancel canx ON cr.providerca = canx.id
LEFT OUTER JOIN applookup_instance canx1 ON canx.lkp_reason = canx1.id
LEFT OUTER JOIN care_catsreferralst rs ON cr.currentsta = rs.id
LEFT OUTER JOIN applookup_instance sl ON rs.lkp_referralst = sl.id
LEFT OUTER JOIN #FirstAppts ON #FirstAppts.ReferralID = cr.id
LEFT OUTER JOIN #LastAppts ON #LastAppts.ReferralID = cr.id
LEFT OUTER JOIN #FutureAppts ON #FutureAppts.ReferralID = cr.id
LEFT OUTER JOIN #BreachUA ON #BreachUA.ReferralID = cr.id
LEFT OUTER JOIN #Investigations ON #Investigations.ReferralID = cr.id
LEFT OUTER JOIN #FutureInvestigations ON #FutureInvestigations.ReferralID = cr.id
LEFT OUTER JOIN #TLTBookings ON #TLTBookings.ReferralID = cr.id
LEFT OUTER JOIN #TLTBookingsAppts ON #TLTBookingsAppts.ReferralID = cr.id
LEFT OUTER JOIN #UserAssesment ON #UserAssesment.ReferralID = cr.id
WHERE cr.discharged IS NULL
AND cr.providerCa IS NULL
AND cr.rejectRefe IS NULL
AND PI.c_val NOT IN ('9990141444', '7048628863', '3000000003', '6188490774', '4786214116',
'7048628863', '9111111194', '7048481595', '4111111149', '9990019924',
'3111111113', '4111111114', '5111111115', '6111111116', '7111111117',
'4523655987', '8777777778')
AND S.serviceNam NOT LIKE '%Ophthalmology%'
AND S.servicenam NOT LIKE '%Lincs%'
AND cr.id = 89759
) TEMP
) TEMP
ORDER BY DateOfReferral
--**************************
DROP TABLE #TLTBookingsAppts
DROP TABLE #TLTBookings
DROP TABLE #Investigations
DROP TABLE #FutureInvestigations
DROP TABLE #FutureAppts
DROP TABLE #LastAppts
DROP TABLE #FirstAppts
DROP TABLE #BreachUA
DROP TABLE #UserAssesment
DROP TABLE #UA
DROP TABLE #SequenceAll
--DROP TABLE #SequenceB
--DROP TABLE #SequenceC
--DROP TABLE #SequenceD
August 2, 2011 at 9:51 am
In full agreement with everybody who's posted so far - this is a lot of work, and you have a lot to learn. Here's a start - you need to know which joins are required in your queries, and which are not. You're joining tables when you don't need to. You're also blindly outer-joining everything. You should KNOW the relationships between your tables. Avoid running almost-identical queries several times - figure out how to pull all the results in one go, then split them out from the result. The first four queries should give a really good example for you and others to work on and elaborate:
SELECT DISTINCT c1.id ReferralID
,a1.id UAID
,a7.c_sequen
,c7.c_te QuestionInForm
,c13.optiontext AnswerOption
,a16.dateanswer ExpiryDate
INTO #SequenceABCD
FROM care_catsreferral c1 -- output
LEFT OUTER JOIN asse_patientassessi a1 ON c1.carecontex = a1.carecontex -- output
LEFT OUTER JOIN asse_patientassess2 a2 ON a1.assessment = a2.id -- join
INNER JOIN asse_userassessment a3 ON a2.userassess = a3.id -- filter
LEFT OUTER JOIN asse_patientassess3 a6 ON a2.id = a6.asse_patientassess2_answergrou -- join
INNER JOIN asse_assessmentpatq a7 ON a6.id = a7.asse_patientassess3_assessmen2 -- output & join & filter
LEFT OUTER JOIN asse_patientassess4 a13 ON a7.id = a13.asse_assessmentpatq_patientans -- join
LEFT OUTER JOIN asse_userassessmen2 a15 ON a7.assessment = a15.id -- join
LEFT OUTER JOIN asse_patientanswerd a16 ON a13.id = a16.asse_patientassess4_answerdeta -- output & join
--LEFT OUTER JOIN core_hcp c3 ON a13.authoringiauthoringh = c3.id
LEFT OUTER JOIN core_answeroption c13 ON a16.picklist = c13.id -- output
--LEFT OUTER JOIN core_memberofstaff c6 ON c3.id = c6.hcp
LEFT OUTER JOIN core_questioninform c7 ON a15.question = c7.id -- output
WHERE a3.name = 'Undischarged'
AND a7.c_sequen IN (0,1,3,4)
--**************************
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 2, 2011 at 10:09 am
Guys that's why I said to run a trace with statement completed + statistics profile XML. That gives the actual execution plan for each step.
When you find one that hangs you can pull the plan from the cache (or maybe add another even that gets this without the stats) and then we can help pinpoint a few things.
But I stand by my original estimate. This is a monster proc that will take days to tune to any decent speed or any thorough review.
We can probably cut it down to size by working together but expect this to run over 1-4 weeks over this thread vs a couple days in person... and in person you'd get better results.
Time to call in a pro IM[NS]HO.
August 2, 2011 at 12:09 pm
To add what the others already stated: whatever answer you'll get from the folks around: most of it is guessing (usually based on best practice/experience).
The concept Chris and ColdCoffee suggested is in general a good approach. If there are missing indexes, it might already speed up the query significantly. Or it may just a little.
My personal guess would be the last query consuming most of the time.
There are tons of issue to clean up.
One of it can be to replace
CASE
WHEN (
SELECT TOP 1 SBA.appointmen
FROM schl_booking_appoin SBA
LEFT OUTER JOIN schl_sess_slot ss ON SBA.id = ss.appointmen
LEFT OUTER JOIN core_activity a1 ON ss.activity = a1.id
WHERE care_catsreferral_appointmen = cr.id
AND sba.appointmen + sba.apptstartt >= GETDATE()
AND SBA.lkp_apptstatus <> -568
AND a1.name IS NOT NULL
ORDER BY SBA.appointmen
) IS NOT NULL
With an equivalent WHEN EXISTS(SELECT 1) query to avoid the TOP 1 ORDER BY.
I'm sure there will be other (fragmented) recommendations. But to really get the query tuned, get someone in for a few days.
And while you're waiting for the budget getting approved, investigate the business case why an INNER JOIN seems to be prohibited...
August 2, 2011 at 12:29 pm
I always shudder when i see that many LEFT JOINs as well as SELECT INTO's.......
As stated by others, from a quick glance over it there are a number of things that could be investigated / done to improve this but personally due to the sheer size of it i'd look to download sql sentry's plan explorer (Free!!!) and throw it through that and see what it believes to be the biggest hitter. May have to run the actual execution plan as opposed to the estimated due to the zillion select into temp tables.
Can be difficult to tune large procs like this with many temp tables as you'r throwing potential tempdb bottleneck's into the equation, depending on the recordsets being inserted into them.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 2, 2011 at 12:33 pm
ChrisTaylor (8/2/2011)
I always shudder when i see that many LEFT JOINs as well as SELECT INTO's.......As stated by others, from a quick glance over it there are a number of things that could be investigated / done to improve this but personally due to the sheer size of it i'd look to download sql sentry's plan explorer (Free!!!) and throw it through that and see what it believes to be the biggest hitter. May have to run the actual execution plan as opposed to the estimated due to the zillion select into temp tables.
Can be difficult to tune large procs like this with many temp tables as you'r throwing potential tempdb bottleneck's into the equation, depending on the recordsets being inserted into them.
He can't even run the proc atm. So we can't get actual plan.
Nice tool tho, it's my fav for plans!
August 2, 2011 at 2:19 pm
All, Thanks for the help and advice... erm where to start with the replies 🙂
I know you all are doing this in your free time and very grateful you took time to read through. I'm no way expecting someone to do it for me. thats not what I am about, I want to learn better ways if how I can improve my coding and understanding of SQL Server.
Ninja's_RGR'us - I have had this query run anywhere from 5 mins upto around 55 mins. I dare not put it anywhere near a web page as yet so if I manage to get it to run is dumped into excel.
My problem with using any profiling is that as the database is held on external servers that are outsourced its very difficult and such a slow procedure to get any help/advice or even privilages changes. Currently I have read only access and run somethings through Linked Server or direct on the server via a saved query. I think as you have said I need to put some pressure on the external people to give me a little help from the DBA. In the meantime I'll have a look at each section again and apply logic that you guys have suggested to it. Its a nightmare of a query.
AlexSQLForums - I havent skipped the Report Status section. The bit i have taken out and then run is the section around the UserAssessment. Thats my worst bit as I've been unsure of how I could do this section better. but reading down the posts it seems that ColdCoffee & ChrisM@Work have given me ideas of how I could make this more efficient
LutzM - How can I tell if there are missing Indexes?
ChrisTaylor - I'll download sql sentry's plan explorer and give it a look
Jez
August 2, 2011 at 2:28 pm
Sql Sentry is awesome, but you still need a .sqlplan to work with it.
Missing indexes are contained in the actual plan.
If it "only" takes 55 minutes to run just let it go and post the plan here. We can't really be usefull and effective without that (sorry for the bad news).
Without profiler you just have to bite the bullet and let it run to completion once...
Let us know once you have all that info.
Make sure it returns the actual plan... or you'll have to go through it all over again!
August 2, 2011 at 3:32 pm
Ninja's_RGR'us (8/2/2011)
Sql Sentry is awesome, but you still need a .sqlplan to work with it.Missing indexes are contained in the actual plan.
If it "only" takes 55 minutes to run just let it go and post the plan here. We can't really be usefull and effective without that (sorry for the bad news).
Without profiler you just have to bite the bullet and let it run to completion once...
Let us know once you have all that info.
Make sure it returns the actual plan... or you'll have to go through it all over again!
Will do, I'll look into that tomorrow and post the results when I get them.
Thanks for help and advice so far.
August 2, 2011 at 3:39 pm
jez.lisle (8/2/2011)
Ninja's_RGR'us (8/2/2011)
Sql Sentry is awesome, but you still need a .sqlplan to work with it.Missing indexes are contained in the actual plan.
If it "only" takes 55 minutes to run just let it go and post the plan here. We can't really be usefull and effective without that (sorry for the bad news).
Without profiler you just have to bite the bullet and let it run to completion once...
Let us know once you have all that info.
Make sure it returns the actual plan... or you'll have to go through it all over again!
Will do, I'll look into that tomorrow and post the results when I get them.
Thanks for help and advice so far.
HTH... this is just the begining.
Day 1 of ? complete 😛
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply