2
\$\begingroup\$

The code below takes 5-6 hours for a years worth of data (850,000 records). It takes much less time when running a single month.

To start with is there anything glaringly obvious within this script which may account for the slowness. Are there any tests I can do to determine where the flaws are. I kind of find it off it takes (currently) 5 hours just to move data from a set of tables into another table.

Is there a different approach which can be used other then running this script each month?

The big select statement at the beginning takes seconds so the issue isn't there.

USE HealthBI
GO

-- basic statement returns 292520 rows on 20/07/2015
-- basic statement returns 318249 rows on 05/08/2015
/** 28/09/2015 (PJ) Updated statement to pull CCG of Res from CDO_CDS_62_OPS_020_02_PATIENT_IDENTITY
 rather than CDO_ODS_EXTENDED_POSTCODE table (Note: HA still coming from CDO_ODS_EXTENDED_POSTCODE)
 Replaced CAST(POSTCODE.CCG_OF_RESIDENCE AS VARCHAR(5)) with CAST(PATIENT_IDENTITY.ORGANISATION_CODE_RESIDENCE_RESPONSIBILITY AS VARCHAR(5)) **/
-- 01/12/2015 (PJ) Added population of OldCDSIdentifier field with the attendance OID to enable easy linking between data and HealthBI tables
-- 02/08/2016 (JB) Tidied/formatted code
-- 07/11/2016 (PJ) Added diagnosis code into statements below
-- Note: May need to add additional code below to suppress fictitious 'Z000' code.

INSERT INTO [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData ([CDSType],[CDSIdentifier],
[ProviderCode],[PCTOfResidence],[PCT],[HACodeOfResidence],[PostcodeOfUsualAddress],
[SiteCodeOfTreatment],[AdministrativeCategory],[NAC_HRG],[LocalClinicCode],
[LocalPatientIdentifier],[NHSNumber],[NNNStatusIndicator],[CarerSupportIndicator],[Sex],
[BirthDate],[CodeOfRegisteredGP],[PracticeCodeOfRegisteredGP],[AttendanceTime],[AttendanceDate],
[AttendedDidNotAttend],[FirstAttendance],[MedicalStaffTypeSeeingPatient],[OutcomeOfAttendance],
[LocationTypeCode],[SpecialtyFunctionCode],[ConsultantSpecialtyFunctionCode],[CodeOfConsultant],
[PrimaryDiagnosisICD],[OperationStatus],[PrimaryProcedureOPCS],[PriorityType],[ReferrerCode],
[ReferringOrganisationCode],[ServiceTypeRequested],[SourceOfReferral],
[ReferralRequestReceivedDate],[LastDNACancelledDate],[CasenoteNumber],[LocalSubSpecialty],[UBRN],
[PPI],[OrgCodePPI],[RTTPeriodStatus],[RTTPeriodStart],[RTTPeriodEnd],[AgeAtCDSDate],[ERO_Date],
[UpdateType],[EthnicGroup],[OutpatientID],[SerialNumber],[CDSUpdateDate],[CDSUpdateTime],
[FinancialYear],[FileNumber],[NationalSpecialty],[InternalNo],[EpisodeNo],[OldCDSIdentifier]) 

SELECT  CAST(TRANSACTION_HEADER.CDS_TYPE AS VARCHAR(3))
        ,CAST(TRANSACTION_HEADER.CDS_UNIQUE_ID AS VARCHAR(40))
        ,CAST(SERVICE_AGREEMENT.ORGANISATION_CODE_CODE_OF_PROVIDER AS VARCHAR(5))
        ,CAST(PATIENT_IDENTITY.ORGANISATION_CODE_RESIDENCE_RESPONSIBILITY AS VARCHAR(5))
        ,CAST(GP_PRACTICE.CCG_PARENT_ORGANISATION_CODE AS VARCHAR(5))
        ,CAST(POSTCODE.ORGANISATION_CODE_HA AS VARCHAR(3))
        -- FIND THE PATIENT WHO HAS POSTCODE HU12  0HE (NOTE THE TWO SPACES!)
        ,CAST(REPLACE(PATIENT_IDENTITY.POSTCODE_OF_USUAL_ADDRESS,'  ',' ') AS VARCHAR(8)) USUAL_POSTCODE
        ,CAST(LOCATION.SITE_CODE_OF_TREATMENT AS VARCHAR(5))
        ,CAST(MAIN.ADMINISTRATIVE_CATEGORY_CODE AS VARCHAR(2))
        ,CAST(OP_APPT.HRG_CODE AS VARCHAR(5))
        ,CAST(  CASE    WHEN SUBSTRING(LOCATION.CLINIC_CODE,1,3) = 'HWS' THEN SUBSTRING(LOCATION.CLINIC_CODE,4,LEN(LOCATION.CLINIC_CODE)) 
                        ELSE LOCATION.CLINIC_CODE 
                END AS VARCHAR(8)) AS LOCAL_CLINIC_CODE
        ,CAST(PATIENT_IDENTITY.LOCAL_PATIENT_IDENTIFIER AS VARCHAR(10))
        ,CAST(PATIENT_IDENTITY.NHS_NUMBER AS VARCHAR(10))
        ,CAST(PATIENT_IDENTITY.NHS_NUMBER_STATUS_INDICATOR_CODE AS VARCHAR(2))
        ,CAST(PATIENT_CHARACTERISTICS.CARER_SUPPORT_INDICATOR AS VARCHAR(2))
        ,CAST(PATIENT_CHARACTERISTICS.PERSON_GENDER_CODE_CURRENT AS VARCHAR(1))
        ,CAST(REPLACE(PATIENT_IDENTITY.PERSON_BIRTH_DATE,'-','') AS VARCHAR(8)) DOB
        ,CAST(GP_REGISTRATION.GENERAL_MEDICAL_PRACTITIONER_SPECIFIED AS VARCHAR(8))
        ,CAST(GP_REGISTRATION.GENERAL_MEDICAL_PRACTICE_CODE_PATIENT_REGISTRATION AS VARCHAR(6))
        ,CAST(REPLACE(SUBSTRING(MAIN.APPOINTMENT_TIME,1,5),':','') AS VARCHAR(4)) APPT_TIME
        ,CAST(REPLACE(MAIN.APPOINTMENT_DATE,'-','') AS VARCHAR(8)) APPT_DATE
        ,CAST(MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE AS VARCHAR(1))
        ,CAST(MAIN.FIRST_ATTENDANCE_CODE AS VARCHAR(1))
        ,CAST(MAIN.MEDICAL_STAFF_TYPE_SEEING_PATIENT AS VARCHAR(2))
        ,CAST(MAIN.OUTCOME_OF_ATTENDANCE_CODE AS VARCHAR(1))
        ,NULL AS ACTIVITY_LOCATION_TYPE_CODE
        ,CAST(CONSULTANT.CARE_PROFESSIONAL_MAIN_SPECIALTY_CODE AS VARCHAR(3))
        ,CAST(  CASE    WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '313' THEN '316' 
                        WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '172' THEN '170'                                     
                        WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '173' THEN '170'                                         
                        ELSE CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE 
                END AS VARCHAR(3))
        ,CAST(CONSULTANT.CONSULTANT_CODE AS VARCHAR(8))
        ,CAST(ICD_PRIMARY.PRIMARY_DIAGNOSIS_ICD AS VARCHAR(4))      
        ,CAST(MAIN.OPERATION_STATUS_CODE AS VARCHAR(1))
        ,CAST(OPCS_PRIMARY.PRIMARY_PROCEDURE_CODE_OPCS AS VARCHAR(4))
        ,CAST(REFERRAL.PRIORITY_TYPE_CODE AS VARCHAR(1))
        ,CAST(REFERRER.REFERRER_CODE AS VARCHAR(8))
        ,CAST(  CASE    WHEN REFERRER.REFERRING_ORGANISATION_CODE = 'SPIREHERH' THEN NULL 
                        ELSE REFERRER.REFERRING_ORGANISATION_CODE 
                END AS VARCHAR(6)) AS REF_ORG
        ,CAST(REFERRAL.SERVICE_TYPE_REQUESTED_CODE AS VARCHAR(1))
        ,CAST(REFERRAL.SOURCE_OF_REFERRAL_FOR_OUT_PATIENTS AS VARCHAR(2))
        ,CAST(REPLACE(REFERRAL.REFERRAL_REQUEST_RECEIVED_DATE,'-','') AS VARCHAR(8)) REF_REC_DATE
        ,CAST(REPLACE(MISSED_APPOINTMENT.LAST_DNA_OR_PATIENT_CANCELLED_DATE,'-','') AS VARCHAR(8)) LAST_DNA_CANC_DATE
        ,CAST(PATIENT_IDENTITY.LOCAL_PATIENT_IDENTIFIER AS VARCHAR(10)) CASENOTE_NUMBER
        -- ward
        -- funding
        -- cancdate
        -- cancby
        -- dind
        -- attcat
        -- appreq
        -- gprdiag
        -- refhosp
        -- appthosp
        -- gpfh
        ,CAST(CONSULTANT.LOCAL_SUB_SPECIALTY_CODE AS VARCHAR(3))
        ,CAST(PATIENT_PATHWAY.UNIQUE_BOOKING_REFERENCE_NUMBER AS VARCHAR(12))
        -- SOME ARE UP TO 23 CHARS LONG
        ,SUBSTRING(PATIENT_PATHWAY.PATIENT_PATHWAY_IDENTIFIER,1,20) AS PPI
        ,CAST(PATIENT_PATHWAY.ORGANISATION_CODE_PATIENT_PATHWAY_IDENTIFIER AS VARCHAR(5))
        ,CAST(PATIENT_PATHWAY.REFERRAL_TO_TREATMENT_PERIOD_STATUS AS VARCHAR(2))
        ,CAST(REPLACE(PATIENT_PATHWAY.REFERRAL_TO_TREATMENT_PERIOD_START_DATE,'-','') AS VARCHAR(8)) RTT_START
        ,CAST(REPLACE(PATIENT_PATHWAY.REFERRAL_TO_TREATMENT_PERIOD_END_DATE,'-','') AS VARCHAR(8)) RTT_END
        ,CAST(MAIN.AGE_AT_CDS_ACTIVITY_DATE AS VARCHAR(3))
        ,CAST(REPLACE(MAIN.EARLIEST_REASONABLE_OFFER_DATE,'-','') AS VARCHAR(8)) ERO_DATE
        ,CAST('9' AS VARCHAR(1)) UPDATETYPE
        -- localappttype
        ,CAST(PATIENT_CHARACTERISTICS.ETHNIC_CATEGORY AS VARCHAR(2))
        ,OP_APPT.UNIQUE_ID  -- outpatientID
        ,CAST('HEY' AS VARCHAR(6)) SERIALNUMBER
        ,CAST(CONVERT(CHAR(8), GETDATE(), 112) AS VARCHAR(8)) CDSUpdateDate
        ,CAST(REPLACE(CONVERT(CHAR(5), GETDATE(), 108),':','') AS VARCHAR(4)) CDSUpdateTime

        -- 02/06/2016 (JB) Not needed, but left in as useful around fiscal change when processing multiple months together (M12 Freeze + M01 Flex)
        ,   CASE    WHEN CONVERT(DATE,MAIN.APPOINTMENT_DATE) >= 'APRIL 1 2016' THEN '2016/2017'
                    ELSE '2015/2016'
            END AS FinancialYear

        ,(SELECT        MAX(FileNumber) + 100
        FROM        [CHH-SLAMDB].[SLAMS_SQL].[dbo].[ImportLog]
        WHERE       ActivityType = 'Outpatient') -- File Number, used to be manually changed every time (EIR & Live)

        ,CAST(  CASE    WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '313' THEN '316' 
                        WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '172' THEN '170'
                        WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '173' THEN '170'
                        ELSE CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE 
                END AS VARCHAR(3))
        ,'NA' InternalNo
        ,'NA' EpisodeNo
        ,CAST(MAIN.ATTENDANCE_IDENTIFIER AS VARCHAR(35))
FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER TRANSACTION_HEADER LEFT JOIN
        dbo.CDO_CDS_62_OPS_020_07_ATTENDANCE_OCCURRENCE_ACTIVITY [MAIN] ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MAIN.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = MAIN.EXTRACT_ID LEFT JOIN 
        dbo.CDO_CDS_62_OPS_020_08_ATTENDANCE_OCCURRENCE_SERVICE_AGREEMENT SERVICE_AGREEMENT ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = SERVICE_AGREEMENT.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = SERVICE_AGREEMENT.EXTRACT_ID LEFT JOIN
        dbo.CDO_CDS_62_OPS_020_02_PATIENT_IDENTITY PATIENT_IDENTITY ON
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = PATIENT_IDENTITY.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = PATIENT_IDENTITY.EXTRACT_ID LEFT JOIN
        dbo.CDO_ODS_EXTENDED_POSTCODE POSTCODE ON
                PATIENT_IDENTITY.POSTCODE_OF_USUAL_ADDRESS = POSTCODE.POST_CODE_VARYING LEFT JOIN
        dbo.CDO_CDS_62_OPS_020_12_GP_REGISTRATION GP_REGISTRATION ON
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = GP_REGISTRATION.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = GP_REGISTRATION.EXTRACT_ID LEFT JOIN 
        dbo.CDO_ODS_GENERAL_MEDICAL_PRACTICE GP_PRACTICE ON 
                GP_REGISTRATION.GENERAL_MEDICAL_PRACTICE_CODE_PATIENT_REGISTRATION = GP_PRACTICE.ORGANISATION_CODE LEFT JOIN 
        dbo.CDO_CDS_62_OPS_020_11_ATTENDANCE_OCCURRENCE_LOCATION_GROUP LOCATION ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = LOCATION.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = LOCATION.EXTRACT_ID LEFT JOIN 
        dbo.CDO_OP_APPOINTMENT OP_APPT ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = OP_APPT.APPOINTMENT_IDENTIFIER LEFT JOIN
        dbo.CDO_CDS_62_OPS_020_03_PATIENT_CHARACTERISTICS PATIENT_CHARACTERISTICS ON
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = PATIENT_CHARACTERISTICS.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = PATIENT_CHARACTERISTICS.EXTRACT_ID LEFT JOIN
        dbo.CDO_CDS_62_OPS_020_04_CARE_EPISODE_PERSON CONSULTANT ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = CONSULTANT.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = CONSULTANT.EXTRACT_ID LEFT JOIN 
        dbo.CDO_CDS_62_OPS_020_09A_ATTENDANCE_OCCURRENCE_CLINICAL_ACTIVITY_OPCS_PRIMARY OPCS_PRIMARY ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = OPCS_PRIMARY.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = OPCS_PRIMARY.EXTRACT_ID LEFT JOIN 
        dbo.CDO_CDS_62_OPS_020_13_REFERRAL_ACTIVITY REFERRAL ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = REFERRAL.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = REFERRAL.EXTRACT_ID LEFT JOIN
        dbo.CDO_CDS_62_OPS_020_14_REFERRER REFERRER ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = REFERRER.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = REFERRER.EXTRACT_ID LEFT JOIN 
        dbo.CDO_CDS_62_OPS_020_15_MISSED_APPOINTMENT MISSED_APPOINTMENT ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MISSED_APPOINTMENT.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = MISSED_APPOINTMENT.EXTRACT_ID LEFT JOIN 
        dbo.CDO_CDS_62_OPS_020_01_PATIENT_PATHWAY PATIENT_PATHWAY ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = PATIENT_PATHWAY.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = PATIENT_PATHWAY.EXTRACT_ID LEFT JOIN
        dbo.CDO_CDS_62_OPS_020_05A_CARE_EPISODE_CLINICAL_DIAGNOSIS_ICD_PRIMARY ICD_PRIMARY ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = ICD_PRIMARY.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = ICD_PRIMARY.EXTRACT_ID
WHERE   TRANSACTION_HEADER.EXTRACT_ID = (   SELECT  MAX(EXTRACT_ID) 
                                            FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER)
        AND TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER NOT LIKE '%NDC'
        AND MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE IN ('3','5','6','7')
        AND TRANSACTION_HEADER.CDS_ACTIVITY_DATE < CONVERT(CHAR(10), GETDATE(), 120)
        AND CONVERT(DATE,MAIN.APPOINTMENT_DATE) >= 'APRIL 1 2016'
        AND CONVERT(DATE,MAIN.APPOINTMENT_DATE) < 'APRIL 1 2017' -- END DATE, Needs Changing Every (EIR) Time
        AND SUBSTRING(LOCATION.CLINIC_CODE,1,3) <> 'HWS'

-- 16/11/15 (JB) Added end date where condition, to prevent additional data for current month and additional deletion step after this script
-- 24/08/15 (PJ) Removed ATTENDED_OR_DID_NOT_ATTEND_CODE '2' (cancelled by patient) from WHERE clause above
-- 24/08/15 (PJ) Only very small number of cancellations were previously included in CDS and submitted to SUS
-- as the majority had a NULL attend code field which meant the clinicom CDS ignored these.

IF OBJECT_ID('tempdb..#TMP_PROC_PIVOT') IS NOT NULL
BEGIN
    DROP TABLE #TMP_PROC_PIVOT
END

SELECT      TRANSACTION_HEADER.CDS_UNIQUE_ID
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 1 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn2
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 2 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn3
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 3 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn4
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 4 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn5
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 5 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn6
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 6 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn7
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 7 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn8
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 8 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn9
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 9 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn10
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 10 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn11
            ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 11 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn12
INTO        #TMP_PROC_PIVOT
FROM        dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER TRANSACTION_HEADER INNER JOIN
            dbo.CDO_CDS_62_OPS_020_07_ATTENDANCE_OCCURRENCE_ACTIVITY [MAIN] ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MAIN.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = MAIN.EXTRACT_ID INNER JOIN 
            dbo.CDO_CDS_62_OPS_020_09B_ATTENDANCE_OCCURRENCE_CLINICAL_ACTIVITY_OPCS_SECONDARY OPCS_SECONDARY ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = OPCS_SECONDARY.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = OPCS_SECONDARY.EXTRACT_ID
WHERE       TRANSACTION_HEADER.EXTRACT_ID = (   SELECT  MAX(EXTRACT_ID) 
                                                FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER)
            AND TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER NOT LIKE '%NDC'
            AND MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE IN ('3','5','6','7')
            AND TRANSACTION_HEADER.CDS_ACTIVITY_DATE < CONVERT(CHAR(10), GETDATE(), 120)
GROUP BY    TRANSACTION_HEADER.CDS_UNIQUE_ID

-- 24/08/2015 (PJ) Removed ATTENDED_OR_DID_NOT_ATTEND_CODE '2' (cancelled by patient) from WHERE clause above
UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
SET     ProcedureCode2OPCS = [PROC].operatn2, 
        ProcedureCode3OPCS = [PROC].operatn3, 
        ProcedureCode4OPCS = [PROC].operatn4, 
        ProcedureCode5OPCS = [PROC].operatn5, 
        ProcedureCode6OPCS = [PROC].operatn6, 
        ProcedureCode7OPCS = [PROC].operatn7, 
        ProcedureCode8OPCS = [PROC].operatn8, 
        ProcedureCode9OPCS = [PROC].operatn9, 
        ProcedureCode10OPCS = [PROC].operatn10, 
        ProcedureCode11OPCS = [PROC].operatn11, 
        ProcedureCode12OPCS = [PROC].operatn12
FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS opa INNER JOIN
        #TMP_PROC_PIVOT [PROC]  ON opa.CDSIdentifier COLLATE SQL_Latin1_General_CP1_CI_AS = [PROC].CDS_UNIQUE_ID COLLATE SQL_Latin1_General_CP1_CI_AS

-- 07/11/2016 (PJ) Added code below to populate any instances that have more than 1 diagnosis code (same process as utilised above)
IF OBJECT_ID('tempdb..#TMP_DIAG_PIVOT') IS NOT NULL
BEGIN
    DROP TABLE #TMP_DIAG_PIVOT
END

SELECT      TRANSACTION_HEADER.CDS_UNIQUE_ID
            ,MAX(CASE WHEN ICD_SECONDARY.SEQUENCE_NUMBER = 1 THEN ICD_SECONDARY.SECONDARY_DIAGNOSIS_ICD ELSE NULL END) AS diag2
            ,MAX(CASE WHEN ICD_SECONDARY.SEQUENCE_NUMBER = 2 THEN ICD_SECONDARY.SECONDARY_DIAGNOSIS_ICD ELSE NULL END) AS diag3
INTO        #TMP_DIAG_PIVOT
FROM        dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER TRANSACTION_HEADER INNER JOIN
            dbo.CDO_CDS_62_OPS_020_07_ATTENDANCE_OCCURRENCE_ACTIVITY [MAIN] ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MAIN.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = MAIN.EXTRACT_ID INNER JOIN 
            dbo.CDO_CDS_62_OPS_020_05B_CARE_EPISODE_CLINICAL_DIAGNOSIS_ICD_SECONDARY ICD_SECONDARY ON 
                TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = ICD_SECONDARY.JOIN_APPOINTMENT_IDENTIFIER
                AND TRANSACTION_HEADER.EXTRACT_ID = ICD_SECONDARY.EXTRACT_ID
WHERE       TRANSACTION_HEADER.EXTRACT_ID = (   SELECT  MAX(EXTRACT_ID) 
                                                FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER)
            AND TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER NOT LIKE '%NDC'
            AND MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE IN ('3','5','6','7')
            AND TRANSACTION_HEADER.CDS_ACTIVITY_DATE < CONVERT(CHAR(10), GETDATE(), 120)
GROUP BY    TRANSACTION_HEADER.CDS_UNIQUE_ID

UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
SET     SubsidiaryDiagnosisICD = [DIAG].diag2, 
        SecondaryDiagnosisICD = [DIAG].diag3
FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS opa INNER JOIN
        #TMP_DIAG_PIVOT [DIAG]  ON opa.CDSIdentifier COLLATE SQL_Latin1_General_CP1_CI_AS = [DIAG].CDS_UNIQUE_ID COLLATE SQL_Latin1_General_CP1_CI_AS


-- Added 20/07/2015 (PJ) Delete activity at these clinics as they were previously recorded as VAP local appointment types
DELETE FROM [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
WHERE       LocalClinicCode IN('AIHVC','HLOVC','JDAVC','JEHVC','JGVC','KILVC')

-- Added 20/07/2015 (PJ) populates local diagnostic appt types to try to create consistency with first 2 months
UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
SET     LocalApptType = CASE    WHEN FirstAttendance = 1 THEN 'NDI'
                                WHEN FirstAttendance = 2 THEN 'ODI'
                                WHEN FirstAttendance = 3 THEN 'NDI'
                                WHEN FirstAttendance = 4 THEN 'ODI' 
                                ELSE NULL 
                        END
FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS op INNER JOIN 
        [HealthBI].[dbo].[CDO_CLINIC_OR_FACILITY] cl ON
                op.LocalClinicCode COLLATE SQL_Latin1_General_CP1_CI_AS = cl.CLINIC_CODE COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE   cl.TR_DIAGNOSTIC_CLINIC <> '0'

-- Added 20/07/2015 (PJ) populates local telephone appt types to try to create consistency with first 2 months
UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
SET     LocalApptType = CASE    WHEN FirstAttendance = 3 THEN 'OTP'
                                WHEN FirstAttendance = 4 THEN 'NTP' 
                                ELSE LocalApptType 
                        END
FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData

-- Added 20/07/2015 (PJ) populates local supplementary appt types to try to create consistency with first 2 months
UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
SET     LocalApptType = 'SUP'
WHERE   tblOutpatientData.LocalClinicCode IN('CAUD002','CAUDINIT','CF001','CF002','LEA00TEV','MACFC','OLS002','RVO00V')

-- Added 20/07/2015 (PJ) Delete therapies activity
-- Added 02/03/2016 (JB) Delete therapies activity, added 'OR' as original code above compared 
-- full length clinics in HealthBI.dbo.CDO_CLINIC_OR_FACILITY against 8 letter character 
-- clinics in OutpatientData (as shortened when grabbed above)
DELETE FROM [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
WHERE       LocalClinicCode IN  (SELECT CLINIC_CODE COLLATE SQL_Latin1_General_CP1_CI_AS 
                                FROM    HealthBI.dbo.CDO_CLINIC_OR_FACILITY 
                                WHERE   TR_IS_AHP_CLINIC <> '0') OR
            LocalClinicCode IN  (SELECT     Clinic COLLATE SQL_Latin1_General_CP1_CI_AS 
                                FROM        CommDB.dbo.tblLocalClinicExtension 
                                WHERE       AHPClinic <> '0')

-- Added 13/01/2016 (PJ) Delete non-RWA activity
DELETE FROM [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData 
WHERE       LocalClinicCode IN  (SELECT CLINIC_CODE COLLATE SQL_Latin1_General_CP1_CI_AS 
                                FROM    HealthBI.dbo.CDO_CLINIC_OR_FACILITY 
                                WHERE LOCAL_ORGANISATION_CODE_TRUST <> 'RWA')


-- Added 03/05/2016 (PJ) Updates Outcome of Attendance field for cancellations
UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData
SET     OutcomeOfAttendance =   CASE    WHEN wl.BookingOutcomeCode = 'CC_REMOVEAPE' THEN '1'
                                        WHEN wl.BookingOutcomeCode = 'CC_REMAINAPE' THEN '3'
                                        ELSE OutcomeOfAttendance 
                                END
FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS op INNER JOIN 
        [HealthBI_Views].[dbo].[OP_APPOINTMENT] AS opappt ON 
                op.OldCDSIdentifier COLLATE SQL_Latin1_General_CP1_CI_AS = opappt.ApptID COLLATE SQL_Latin1_General_CP1_CI_AS LEFT JOIN 
        [HealthBI_Views].[dbo].[OP_WAITING_LIST_ENTRY_BOOKING] wl ON
                opappt.BookingOfferID = wl.BookingOfferID
WHERE   op.AttendedDidNotAttend IN('2','4')

-- Update Added 02/08/2016 (JB), Set GP Code and Gp Practice Code to 'R9999981' and 'V81998' when the patient has a postcode that's a prison
UPDATE  [CHH-SLAMDB].[SLAMS_SQL].[dbo].[tblOutpatientData]
SET     [CodeOfRegisteredGP] = 'R9999981',
        [PracticeCodeOfRegisteredGP] = 'V81998',
        [pct] = 'Q72'
WHERE   [PostcodeOfUsualAddress] COLLATE Latin1_General_CI_AS IN    (SELECT DISTINCT    [Postcode] COLLATE Latin1_General_CI_AS
                                                                    FROM                [CHH-BILIVE].[CommDB].[dbo].[tblODSPrison])
\$\endgroup\$
  • 1
    \$\begingroup\$ Hey, welcome to Code Review! It would help if you could add a short description of what your code accomplishes. Have a look at "How do I ask a good question?" for tips on improving your question. \$\endgroup\$ – Graipher Apr 20 '17 at 9:04
  • 2
    \$\begingroup\$ Can you add the query plans of the query ? Even if estimated it might help discovering what may (or do) take a lot of time in your query. Before executing the query in Mangement Studio, press "CTRL+L". \$\endgroup\$ – Hybris95 Apr 20 '17 at 9:05
  • \$\begingroup\$ You can also get the real execution plan if you execute your query by pressing "CTRL+M" before executing the query. \$\endgroup\$ – Hybris95 Apr 20 '17 at 9:05
  • \$\begingroup\$ brentozar.com/pastetheplan/?id=SJtuM7wCl \$\endgroup\$ – Simon Apr 21 '17 at 6:16
1
\$\begingroup\$

The where is breaking the first left join

You are getting max on every row. Get it once and save it in a variable.

|improve this answer|||||
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.