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 HealthBIGO-- 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_IDWHERE   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 NULLBEGIN    DROP TABLE #TMP_PROC_PIVOTENDSELECT      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 operatn12INTO        #TMP_PROC_PIVOTFROM        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_IDWHERE       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 aboveUPDATE  [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].operatn12FROM    [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 NULLBEGIN    DROP TABLE #TMP_DIAG_PIVOTENDSELECT      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 diag3INTO        #TMP_DIAG_PIVOTFROM        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_IDWHERE       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_IDUPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData SET     SubsidiaryDiagnosisICD = [DIAG].diag2,         SecondaryDiagnosisICD = [DIAG].diag3FROM    [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 typesDELETE 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 monthsUPDATE  [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                         ENDFROM    [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_ASWHERE   cl.TR_DIAGNOSTIC_CLINIC <> '0'-- Added 20/07/2015 (PJ) populates local telephone appt types to try to create consistency with first 2 monthsUPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData SET     LocalApptType = CASE    WHEN FirstAttendance = 3 THEN 'OTP'                                WHEN FirstAttendance = 4 THEN 'NTP'                                 ELSE LocalApptType                         ENDFROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData-- Added 20/07/2015 (PJ) populates local supplementary appt types to try to create consistency with first 2 monthsUPDATE  [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 activityDELETE 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 cancellationsUPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientDataSET     OutcomeOfAttendance =   CASE    WHEN wl.BookingOutcomeCode = 'CC_REMOVEAPE' THEN '1'                                        WHEN wl.BookingOutcomeCode = 'CC_REMAINAPE' THEN '3'                                        ELSE OutcomeOfAttendance                                 ENDFROM    [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.BookingOfferIDWHERE   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 prisonUPDATE  [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])
Optimising performance of long running a sql query
performance;sql;t sql