150,305 members

Skip to content. | Skip to navigation

Network navigation
 
 

SQLs

The enquires that the IT system will use to populate the PTL automatically, which should allow this to be built into Provider IT systems. Please note the SQL document may not open for those who do not have a program installed to open a .sql file by default, however we are assured that staff who would be using the PTL (namely analysts) should have software capable of reading it.

Click on the file name to download it, or use the toolbox to upload a new version and/or examine the version history

SQL code icon PTL to Share v1.0.sql — SQL code, 11 KB (11837 bytes)

File contents

/*
Northumberland, Tyne and Wear NHS Foundation Trust (NTW).

EIP Patient Tracking List Report.

The sql code below can be customised and used by other organisations as required.
It is intended to be used as a starting point for measuring EIP waits and will hopefully 
provide a good starting point using the start / stop clock rules used by HSCIC. You will
need to replace the codes used below with codes relevant to your system, this is indicated
throughout the sql statement

It is provided without support and will need to be adapted to your system.  Where
the field name is self-explanatory we have not included comments.


Version Control
	Version 1.0 (14/03/2016) - R.Lyons
		PLEASE NOTE - this has not been fully tested yet within NTW.

*/

declare @RPStartDate as Date = '2016/01/01'
declare @RPEndDate as Date = '2016/03/14'

SELECT VR.ClientID As PatientID,
				VC.PatientInitials,
				VC.NHSNumber,
				VR.ReferralNumber,
				GST.CodeDescription As Team,
				'' As UniqueHSCICIdentifier,
				'' As TrustIdentifier,
				VC.DateofBirth,
				VC.Age,
				CONVERT(Varchar, VR.ReferralDateTime, 103) As ReferralDateToProvider,	
				CONVERT(Varchar, VR.ReferralReceivedDate, 103) As ReferralReceivedDate,
				--Pick up and blank internal referrals
				CASE
					WHEN VR.ReferralSource IN (Select Code from AmsReferralSource where InternalReferral = 1) THEN 'Null' 
					ELSE ARS.CodeDescription 
				END As ExternalReferralSource
				--End of pick up and blank internal referrals
				,
				CONVERT(Varchar, VR.ReferralAcceptedDate, 103) As DateReferralReceivedByEIP,
				VR.ReferralComment As AnyPathwayDelaysForReferral, --We use referral comments
				FA.AssessmentDate1, --Earliest appointment date for the EIP referral
				FA.Attendance1, --Indicates whether or not the client attended the appointment
				FA.AssessmentDate2, --As above but for the second appointment following the first appointment
				FA.Attendance2, --Indicates whether or not not the client attended the appointment
				FA.AssessmentDate3,--As above but for the third appointment following the first appointment
				FA.Attendance3, --Indicates whether or not not the client attended the appointment
				FA.AssessmentDate4,--As above but for the fourth appointment following the first appointment
				FA.Attendance4, --Indicates whether or not not the client attended the appointment
				
				-- Replace these referral discharge codes to your local codes with the national picklist values of 
				-- EIPNFTA (04), EIPPD (06), EIPPRD (07), EIPRTOS(08), EIPRTBS (09), EIPIRR (02)
				CASE 
					WHEN DR.NotAppropriateTransferredTo IN ('EIPNFTA', 'EIPPD', 'EIPPRD', 'EIPRTOS', 'EIPRTBS', 'EIPIRR') THEN GDR.CodeDescription 
					ELSE Null
				END As NotAppropriateTransferredTo
				--End of referral discharge codes
				,
				RA.AllocatedToEIPCareCoordinator, --Date allocated to an EIP Care Coordinator
				CAARMS.FirstCAARMSDate as CAARMSAssessmentStarted, -- Date of when CAARMS assessment started
				
				--Used to display an appropriate status of the referral and wait
				CASE 
					WHEN RA.AllocatedToEIPCareCoordinator IS NOT NULL THEN '111111' --Allocated to Care Coordinator. Change to value relevant to a value to you
					WHEN DR.NotAppropriateTransferredTo IS NOT NULL THEN '222222' --Referral not appropriate. Change to value relevant to a value to you
					WHEN CAARMS.FirstCAARMSDate IS NOT NULL THEN '333333' --Client is ARMS. Change to value relevant to a value to you									
					WHEN DATEDIFF(d, GETDATE(), DATEADD(dd, 14, VR.ReferralDateTime)) < 0 THEN '444444' --Breached. Change to value relevant to a value to you	
					ELSE DATEDIFF(d, GETDATE(), DATEADD(dd, 14, VR.ReferralDateTime)) --Not breach.  Count down of days until breach
				END As DaysToBreach,
				--End of Days to breach status
				--Current client waiting time
				CASE
					WHEN (RA.AllocatedToEIPCareCoordinator IS NULL And DR.NotAppropriateTransferredTo IS NULL
						AND CAARMS.FirstCAARMSDate IS NULL) THEN DATEDIFF(d, VR.ReferralDateTime, GETDATE())
					ELSE NULL
				END As HowLongHasThePatientWaited
				--End of current client waiting time
				,
				'' as DurationOfUntreatedPsychosis,
				VR.DischargeDateTime As DischargeDate,
				GDR.CodeDescription As DischargeReason
from AMSReferral VR --This is the table that contains your EIP Referral
	INNER JOIN (SELECT VC.ClientID,
						LEFT(VC.FirstName, 1) + LEFT(VC.Surname, 1) As PatientInitials,
						VC.NNN As NHSNumber,
						CONVERT(Varchar, VC.DateofBirth, 103) As DateofBirth,
						(DATEDIFF(y, VC.DateofBirth, GetDate())/365) As Age
				FROM 	dbo.Client VC ) VC
				ON		VR.ClientID = VC.ClientID --This is a link to the client demographics information 
	--Get the first 4 appointment dates and attendance status for the referral
	LEFT JOIN  (SELECT ClientID,ReferralNumber,MIN(SequenceID) AS SequenceID,
				MAX(CASE WHEN AppointmentNumber = 1 THEN CONVERT(Varchar, AppointmentDate, 103) END) AS AssessmentDate1,
				MAX(CASE WHEN AppointmentNumber = 1 THEN Attendance END) AS Attendance1,
				MAX(CASE WHEN AppointmentNumber = 2 THEN CONVERT(Varchar, AppointmentDate, 103) END) AS AssessmentDate2,
				MAX(CASE WHEN AppointmentNumber = 2 THEN Attendance END) AS Attendance2,
				MAX(CASE WHEN AppointmentNumber = 3 THEN CONVERT(Varchar, AppointmentDate, 103) END) AS AssessmentDate3,
				MAX(CASE WHEN AppointmentNumber = 3 THEN Attendance END) AS Attendance3,
				MAX(CASE WHEN AppointmentNumber = 4 THEN CONVERT(Varchar, AppointmentDate, 103) END) AS AssessmentDate4,
				MAX(CASE WHEN AppointmentNumber = 4 THEN Attendance END) AS Attendance4
				FROM
					(SELECT AR.ClientID,
							AR.ReferralNumber,
							AAC.SequenceID,
							AA.AppointmentDate,
							CASE
								WHEN AAC.Outcome IS NULL And AA.CancellationDateTime IS NOT NULL THEN 'Cancelled' 
								ELSE AO.CodeDescription 
							END As Attendance,
							ROW_NUMBER () OVER (Partition By AR.ClientID, AR.ReferralNumber ORDER BY AA.AppointmentDate) As AppointmentNumber
					FROM AmsReferral AR
						LEFT JOIN dbo.AmsAppointmentContact AAC ON AAC.ClientID = AR.ClientID and AAC.ReferralID = AR.ReferralNumber
						LEFT JOIN dbo.AmsAppointment AA ON AAC.SequenceID = AA.SequenceID  
						LEFT JOIN dbo.AmsOutcome AO ON AAC.Outcome = AO.Code
					WHERE AR.ReferralReason = 'EIPSFEP' -- Replace with your code that maps to national code of 01 (Suspected) First Episode Psychosis
				) t
				GROUP BY ClientID, ReferralNumber
				) FA
				ON	FA.ClientID = VR.ClientID AND FA.ReferralNumber = VR.ReferralNumber
	--end of get the first 4 appointment dates and attendance status for the referral
	
	--Get the attended appointments and outcome for these appointments
	LEFT JOIN  (SELECT ClientID,ReferralNumber,
					MIN(SequenceID) AS SequenceID, 
					DTS.AppointmentDate As DateTreatmentStarted
				FROM
					(SELECT AR.ClientID,
							AR.ReferralNumber,
							AAC.SequenceID,
							AA.AppointmentDate,
							CASE
								WHEN AAC.Outcome IS NULL And AA.CancellationDateTime IS NOT NULL THEN 'Cancelled' 
								ELSE AO.CodeDescription 
								END As Attendance,
								ROW_NUMBER () OVER (Partition By AR.ClientID, AR.ReferralNumber ORDER BY AA.AppointmentDate) As AppointmentNumber
					 FROM AmsReferral AR
						LEFT JOIN dbo.AmsAppointmentContact AAC ON AAC.ClientID = AR.ClientID and AAC.ReferralID = AR.ReferralNumber
						LEFT JOIN dbo.AmsAppointment AA ON AAC.SequenceID = AA.SequenceID  
						LEFT JOIN dbo.AmsOutcome AO ON AAC.Outcome = AO.Code
					 WHERE AR.ReferralReason = 'EIPSFEP' -- Replace with your code that maps to national code of 01 (Suspected) First Episode Psychosis
					 AND AO.DNA <> 0 -- Replace this condition to only return Attended appointments
					 ) DTS
					 GROUP BY ClientID, ReferralNumber, AppointmentDate
					 ) DTS
					ON	DTS.ClientID = VR.ClientID And	DTS.ReferralNumber = VR.ReferralNumber
	--end of get the attended appointments and outcome for these appointments				

--Get referrals that have ended by None treatment
LEFT JOIN 		(SELECT DR.ClientID,
						DR.ReferralNumber,
						CASE 
							
							WHEN ISNULL(DR.DischargeReason,'') > '' THEN DR.DischargeReason
							ELSE Null
						END As NotAppropriateTransferredTo
				  FROM
				  (SELECT ClientID, 
							ReferralNumber, 
							DischargeReason from Amsreferral
					WHERE DischargeReason IN ('EIPNFTA', 'EIPPD', 'EIPPRD', 'EIPRTOS', 'EIPRTBS', 'EIPIRR')
					-- Replace these referral discharge codes to your local codes with the national picklist values of 
					-- EIPNFTA (04), EIPPD (06), EIPPRD (07), EIPRTOS(08), EIPRTBS (09), EIPIRR (02)
				   ) DR
				   GROUP BY ClientID, ReferralNumber, DischargeReason
				) DR
				   ON	DR.ClientID = VR.ClientID AND DR.ReferralNumber = VR.ReferralNumber
--end of get referrals that have ended by None treatment
--Get the earliest date the client was allocated to an EIP carecoordinator following the referral recieved date
LEFT JOIN	
				(select cc.ClientID, cc.StartDate as AllocatedToEIPCareCoordinator
				,ROW_NUMBER () OVER (Partition By cc.ClientID ORDER BY cc.StartDate) As CCRowNum
				FROM CPACareCoordinator CC
				INNER JOIN GenHCP H ON CC.CareCoordinatorID = H.GenHCPCode 
				INNER JOIN AmsReferral R on CC.ClientID = R.ClientID
				where H.PrimaryTeam IN ('EIP', 'EIPG', 'EIPS', 'EIPST', 'NLANDEIP') --Replace with codes of your EIP teams
				AND R.ReferralReason = 'EIPSFEP' -- Replace with your code that maps to national code of 01 (Suspected) First Episode Psychosis
				AND CC.StartDate >= R.ReferralReceivedDate
				) RA
				ON RA.ClientID = VR.ClientID
				And RA.AllocatedToEIPCareCoordinator >= VR.ReferralReceivedDate
				and RA.AllocatedToEIPCareCoordinator between @RPStartDate and @RPEndDate --Remove or update to work with your report
				AND RA.CCRowNum = 1

--End of get the earliest date the client was allocated to an EIP carecoordinator following the referral recieved date

LEFT JOIN	dbo.GenDischargeReason GDR ON GDR.Code = VR.DischargeReason

--Gets the CAARMS assessment to indication client is ARMS
LEFT JOIN	(select AR.SequenceID,
				MIN(AA.AppointmentDate) As FirstCAARMSDate
						From AmsReferral AR
						LEFT JOIN dbo.AmsAppointmentContact AAC ON AAC.ClientID = AR.ClientID and AAC.ReferralID = AR.ReferralNumber
						LEFT JOIN Dbo.AmsAppointment AA ON AAC.SequenceID = AA.SequenceID
						Left Join dbo.AmsAppointmentContactActivity ACA ON AAC.SequenceID = ACA.SequenceID
						Where ACA.GenActivityCode = 'EIPARMSAC' 
						--Replace with code that relates to ARMS Assessment commenced 
						--SNOMED code for Mental health risk indicator assessment (802551000000107)
						AND ACA.ActualFlag = 1 --Indicates the activity took place
						GROUP BY AR.SequenceID
			)
			CAARMS ON CAARMS.SequenceID = VR.SequenceID
--end of gets the CAARMS assessment to indication client is ARMS			

--Also check to see if referral has been allocated to another EIP team
INNER JOIN	dbo.AmsReferralTeam ART ON ART.ClientID = VR.ClientID and ART.ReferralID = VR.ReferralNumber 
and ART.TeamCode IN ('EIP', 'EIPG', 'EIPS', 'EIPST', 'NLANDEIP') --Replace with codes of your EIP teams
-- End of also check to see if referral has been allocated to another EIP team
INNER JOIN	dbo.GenServiceTeam GST ON ART.TeamCode = GST.Code -- Join to team name table
INNER JOIN  dbo.AmsReferralSource ARS ON ARS.Code = VR.ReferralSource -- Join to Referral Source Table 
WHERE		VR.ReferralReason = 'EIPSFEP' -- Replace with your code that maps to national code of 01 (Suspected) First Episode Psychosis
AND			VR.TeamReferredTo IN ('EIP', 'EIPG', 'EIPS', 'EIPST', 'NLANDEIP') --Replace with codes of your EIP teams
ORDER BY	VR.clientID