question

SteveGriffin-8463 avatar image
0 Votes"
SteveGriffin-8463 asked MelissaMa-msft commented

Query optimization

I'm struggling to optimize a report query. It's quite extensive.. When I use start and end dates of 1/1/2016 to today, it is taking hours to complete. My users are telling me this used to complete within 10-15 minutes.

 WITH cteq_vars AS (
     SELECT 
         TO_DATE(TO_CHAR(:From_Date, 'mm/dd/yyyy'), 'mm/dd/yyyy') From_Dt,
         TO_DATE(TO_CHAR(:to_date, 'mm/dd/yyyy'), 'mm/dd/yyyy') To_Dt,
         CASE WHEN NVL(UPPER(TRIM(:account_no)), '* ALL *') = ''
             THEN '* ALL *'
             ELSE NVL(UPPER(TRIM(:account_no)), '* ALL *')
         END account_number,  
         CASE WHEN :Paid_From = '' OR :Paid_From IS NULL
             THEN NULL
             ELSE TO_DATE(:Paid_From, 'mm/dd/yyyy')
         END  Paid_From,
         CASE WHEN :Paid_To = '' OR :Paid_To IS NULL
             THEN NULL
             ELSE TO_DATE(:Paid_To, 'mm/dd/yyyy')
         END  Paid_To,
         CASE WHEN :Rsrv_Date = '' OR :Rsrv_Date IS NULL
             THEN TRUNC(SYSDATE)
             ELSE TO_DATE(:Rsrv_Date, 'mm/dd/yyyy')
         END  reserve_date
     FROM DUAL
 ),
    
 cteq_max_pob_no AS (
     SELECT    
         MAX(ipob1.injr_pob_no) injr_pob_no,
         ipob1.injr_id injr_id,
         UPPER(TRIM(ipob1.pob_cd)) pob_cd,
         UPPER(TRIM(ipob1.pob_loc_cd)) pob_loc_cd
     FROM    
         INJURY_PART_OF_BODY ipob1
     WHERE    
         UPPER(TRIM(ipob1.injr_pob_pri_ind)) = 'Y'
     GROUP BY 
         ipob1.injr_id,
         ipob1.pob_cd,
         ipob1.pob_loc_cd 
 ),
    
 cteq_1 AS (
     SELECT 
         c.injr_id injr_id,
         TRIM(UPPER(TRIM(c.clm_no))|| ' ' ||
             CASE UPPER(TRIM(cthvc.clm_typ_cd))
                 WHEN 'WAGEL' THEN 'T'
                 WHEN 'MED' THEN 'N'
                 WHEN 'X' THEN 'X'
                 ELSE NULL 
             END
         ) "Claim Number",
            
         CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'
             THEN ''
             ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))
         END "IW Name",
         INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",
         UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",
         TRUNC(MONTHS_BETWEEN(TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",
         TRUNC(i.injr_dtm) "Injury Date",
         TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",
         TRIM(REPLACE(REPLACE(REPLACE(i.INJR_ACDN_DESC_TEXT, CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')) "Injury Description",
         INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",
         UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",
         UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",
         UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",
         UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",
         pb.plcy_no "Account Number",
         INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",
         TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",
         NVL(c.clm_clmt_empl_no_of_yrs, 0) "Years",
         NVL(c.clm_clmt_empl_no_of_mm, 0) "Months",
         NVL(c.clm_clmt_empl_no_of_dd, 0) "Days",
         UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",
         vars.From_Dt vars_From_Dt,
         vars.To_Dt vars_To_Dt,
         vars.account_number account_number,
         vars.Paid_From vars_Paid_From,
         vars.Paid_To vars_Paid_To,
         vars.reserve_date reserve_date
            
     FROM    
         cteq_vars vars
            
         INNER JOIN INJURY i
         ON TRUNC(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt
            
         INNER JOIN CLAIM c
         ON c.injr_id = i.injr_id 
         AND c.clm_clmt_empl_no_of_yrs IS NOT NULL
            
         INNER JOIN POLICY_BUSINESS pb
         ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr
         AND (
             LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')
             OR vars.account_number = '* ALL *'
         )
         AND c.clm_clmt_empl_no_of_yrs < 200
            
         INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv
         ON cshv.injr_id = i.injr_id
         AND cshv.clm_sts_cd != 'x' 
            
         INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc
         ON cthvc.injr_id = i.injr_id 
            
         INNER JOIN PERSON_HISTORY ph
         ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt 
         AND ph.prsn_hist_end_dt IS NULL 
            
         INNER JOIN CLAIM_STATUS cs
         ON UPPER(TRIM(cs.CLM_STS_CD)) = UPPER(TRIM(cshv.clm_sts_cd)) 
            
         LEFT OUTER JOIN EMPLOYMENT_CLASS ec
         ON UPPER(TRIM(ec.EMPL_CLS_CD)) = UPPER(TRIM(c.empl_cls_cd))
            
         INNER JOIN LEGAL_ENTITY le
         ON le.LGL_ENTY_ID = i.lgl_enty_id_emplr
            
         LEFT OUTER JOIN NATURE_OF_INJURY noi
         ON UPPER(TRIM(noi.NOI_CD)) = UPPER(TRIM(i.NOI_CD)) 
            
         LEFT OUTER JOIN OCCUPATION_TYPE ot
         ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))            
            
         LEFT OUTER JOIN cteq_max_pob_no ipob
         ON ipob.injr_id = i.injr_id
            
         LEFT OUTER JOIN PART_OF_BODY pob
         ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd)) 
            
         LEFT OUTER JOIN INJURY_SOURCE si
         ON UPPER(TRIM(si.INJR_SRC_CD)) = UPPER(TRIM(i.INJR_SRC_CD))
            
     UNION ALL
        
     SELECT 
         c.injr_id injr_id,
         TRIM(UPPER(TRIM(c.clm_no)) || ' ' || 
             CASE UPPER(TRIM(cthvc.clm_typ_cd))
                 WHEN 'WAGEL' THEN 'T'
                 WHEN 'MED' THEN 'N'
                 WHEN 'X' THEN 'X'
                 ELSE NULL 
             END
         ) "Claim Number",
            
         CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'
             THEN ''
             ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))
         END "IW Name",
         INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",
         UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",
         TRUNC(MONTHS_BETWEEN (TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",
         TRUNC(i.injr_dtm) "Injury Date",
         TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",
         TRIM(REPLACE(REPLACE(REPLACE(REPLACE(i.injr_acdn_desc_text, CHR (10), ' '), CHR (13), ' '), CHR (9), ' '), '"', '''''')) "Injury Description",
         INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",
         UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",
         UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",
         UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",
         UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",
         pb.plcy_no "Account Number",
         INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",
         TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",
         TRUNC(MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY')) / 12) "Years",
         (
                TRUNC(MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'))
             - TRUNC((MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY')) / 12)) * 12)
         ) "Months",       
         (
             TRUNC(i.injr_dtm) 
             - TRUNC(ADD_MONTHS(TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'), MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'))))
         ) "Days",
         UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",
         vars.From_Dt vars_From_Dt,
         vars.To_Dt vars_To_Dt,
         vars.account_number account_number,
         vars.Paid_From vars_Paid_From,
         vars.Paid_To vars_Paid_To,
         vars.reserve_date reserve_date
            
     FROM    
         cteq_vars vars
            
         INNER JOIN INJURY i
         ON Trunc(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt
            
         INNER JOIN CLAIM c
         ON c.injr_id = i.injr_id 
         AND c.clm_clmt_empl_no_of_yrs IS NOT NULL
         AND c.clm_clmt_empl_no_of_yrs >= 200
            
         INNER JOIN POLICY_BUSINESS pb
         ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr
         AND (
             LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')
             OR vars.account_number = '* ALL *'
         )
            
         INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc
         ON cthvc.injr_id = i.injr_id
            
         INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv
         ON cshv.injr_id = i.injr_id 
         AND UPPER(TRIM(cshv.clm_sts_cd)) NOT IN ('X')
            
         LEFT OUTER JOIN NATURE_OF_INJURY noi
         ON noi.noi_cd = i.noi_cd
            
         INNER JOIN CLAIM_STATUS cs
         ON UPPER(TRIM(cs.clm_sts_cd)) = UPPER(TRIM(cshv.clm_sts_cd))
            
         INNER JOIN PERSON_HISTORY ph
         ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt 
         AND ph.prsn_hist_end_dt IS NULL
            
         INNER JOIN LEGAL_ENTITY le
         ON i.lgl_enty_id_emplr = le.lgl_enty_id
                   
         LEFT OUTER JOIN EMPLOYMENT_CLASS ec
         ON UPPER(TRIM(ec.empl_cls_cd)) = UPPER(TRIM(c.empl_cls_cd))
            
         LEFT OUTER JOIN INJURY_SOURCE si
         ON UPPER(TRIM(si.injr_src_cd)) = UPPER(TRIM(i.injr_src_cd))
            
         LEFT OUTER JOIN OCCUPATION_TYPE ot
         ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))
            
         LEFT OUTER JOIN cteq_max_pob_no ipob
         ON ipob.injr_id = i.injr_id
            
         LEFT OUTER JOIN PART_OF_BODY pob
         ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd))
            
     UNION ALL
        
     SELECT 
         c.injr_id injr_id,
         TRIM(UPPER(TRIM(c.clm_no)) || ' ' || 
             CASE UPPER(TRIM(cthvc.clm_typ_cd))
                 WHEN 'WAGEL' THEN 'T'
                 WHEN 'MED' THEN 'N'
                 WHEN 'X' THEN 'X'
                 ELSE NULL 
             END
         ) "Claim Number",
            
         CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'
             THEN ''
             ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))
         END "IW Name",
         INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",
         UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",
         TRUNC(MONTHS_BETWEEN (TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",
         TRUNC(i.injr_dtm) "Injury Date",
         TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",
         TRIM(REPLACE(REPLACE(REPLACE(REPLACE(i.injr_acdn_desc_text, CHR (10), ' '), CHR (13), ' '), CHR (9), ' '), '"', '''''')) "Injury Description",
         INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",
         UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",
         UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",
         UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",
         UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",
         pb.plcy_no "Account Number",
         INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",
         TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",
         NVL(c.clm_clmt_empl_no_of_yrs, 0) "Years",
         NVL(c.clm_clmt_empl_no_of_mm, 0) "Months",
         NVL(c.clm_clmt_empl_no_of_dd, 0) "Days",
         UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",
         vars.From_Dt vars_From_Dt,
         vars.To_Dt vars_To_Dt,
         vars.account_number account_number,
         vars.Paid_From vars_Paid_From,
         vars.Paid_To vars_Paid_To,
         vars.reserve_date reserve_date
            
     FROM 
         cteq_vars vars
            
         INNER JOIN INJURY i
         ON Trunc(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt
            
         INNER JOIN CLAIM c
         ON c.injr_id = i.injr_id 
         AND c.clm_clmt_empl_no_of_yrs IS NULL
            
         INNER JOIN POLICY_BUSINESS pb
         ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr
         AND (
             LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')
             OR vars.account_number = '* ALL *'
         ) 
                    
         INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc
         ON cthvc.injr_id = i.injr_id
            
         INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv
         ON cshv.injr_id = i.injr_id 
         AND UPPER(TRIM(cshv.clm_sts_cd)) NOT IN ('X')
            
         LEFT OUTER JOIN NATURE_OF_INJURY noi
         ON UPPER(TRIM(noi.noi_cd)) = UPPER(TRIM(i.noi_cd))
            
         INNER JOIN CLAIM_STATUS cs
         ON UPPER(TRIM(cs.clm_sts_cd)) = UPPER(TRIM(cshv.clm_sts_cd))
            
         INNER JOIN PERSON_HISTORY ph
         ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt 
         AND ph.prsn_hist_end_dt IS NULL
            
         INNER JOIN LEGAL_ENTITY le
         ON i.lgl_enty_id_emplr = le.lgl_enty_id
                    
         LEFT OUTER JOIN EMPLOYMENT_CLASS ec
         ON UPPER(TRIM(ec.empl_cls_cd)) = UPPER(TRIM(c.empl_cls_cd))
            
         LEFT OUTER JOIN INJURY_SOURCE si
         ON UPPER(TRIM(si.injr_src_cd)) = UPPER(TRIM(i.injr_src_cd))
            
         LEFT OUTER JOIN OCCUPATION_TYPE ot
         ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))
            
         LEFT OUTER JOIN cteq_max_pob_no ipob
         ON ipob.injr_id = i.injr_id
            
         LEFT OUTER JOIN PART_OF_BODY pob
         ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd))
            
     ORDER BY 1
 ),
 --Query completes in 34 secs up to this point, same amount of rows.  19623
 cteq_2_1 AS (
     SELECT 
         cft.injr_id,
         UPPER(TRIM(asda.bnft_typ_cd)) bnft,
         ((Trunc(asd.admis_sch_dtl_drv_prd_end_dt) - Trunc(asd.admis_sch_dtl_drv_prd_eff_dt)) + 1 ) ttdays,
         0 tpdays
            
     FROM    
         cteq_1 cte1
            
         INNER JOIN CLAIM_FINANCIAL_TRAN cft
         ON cft.injr_id = cte1.injr_id
         AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE' 
         AND UPPER(TRIM(cft.bnft_typ_cd)) IN ('TT', 'ABP', 'PT', 'RHBT', 'RHBP') 
         AND (
             (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
             AND 
             (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
         )
         INNER JOIN ADMISSION_SCHEDULE_DETAIL_AMT asda
         ON cft.cft_id = asda.cft_id 
            
         INNER JOIN ADMISSION_SCHEDULE_DETAIL asd
         ON asd.admis_sch_id = asda.admis_sch_id 
         AND asd.admis_sch_dtl_no = asda.admis_sch_dtl_no 
            
         INNER JOIN ADMISSION_PERIOD ap
         ON asd.admis_sch_id = ap.admis_sch_id 
         AND UPPER(TRIM(ap.admis_prd_void_ind)) = 'N' 
            
         INNER JOIN ADMISSION_PERIOD_RATE_HISTORY aprh
         ON aprh.injr_id = ap.injr_id 
         AND aprh.admis_no = ap.admis_no 
         AND aprh.admis_prd_no = ap.admis_prd_no 
         AND UPPER(TRIM(aprh.admis_prd_rt_hist_void_ind)) = 'N' 
         AND aprh.admis_prd_rt_hist_no = (SELECT Max(aprh2.admis_prd_rt_hist_no)
                                          FROM ADMISSION_PERIOD_RATE_HISTORY aprh2
                                          WHERE aprh2.injr_id = aprh.injr_id 
                                          AND aprh2.admis_no = aprh.admis_no 
                                          AND aprh2.admis_prd_no = aprh.admis_prd_no)
 ),
 --1m 17sec
 cteq_2_2 AS (
     SELECT 
         cft.injr_id injr_id,
         UPPER(TRIM(asda.bnft_typ_cd)) bnft,
         0 ttdays,
         ((Trunc(asd.admis_sch_dtl_drv_prd_end_dt) - Trunc(asd.admis_sch_dtl_drv_prd_eff_dt)) + 1) tpdays
            
     FROM 
         cteq_1 cte1
            
         INNER JOIN CLAIM_FINANCIAL_TRAN cft
         ON cft.injr_id = cte1.injr_id
         AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE' 
         AND UPPER(TRIM(cft.bnft_typ_cd)) IN ('PP', 'PDB', 'PRB', 'TP') 
         AND (
             (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
             AND 
             (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
         )
         INNER JOIN ADMISSION_PERIOD ap
         ON ap.injr_id = cte1.injr_id
         AND UPPER(TRIM(ap.admis_prd_void_ind)) = 'N' 
            
         INNER JOIN ADMISSION_PERIOD_RATE_HISTORY aprh
         ON aprh.injr_id = ap.injr_id 
         AND aprh.admis_no = ap.admis_no 
         AND aprh.admis_prd_no = ap.admis_prd_no
         AND UPPER(TRIM(aprh.admis_prd_rt_hist_void_ind)) = 'N' 
         AND aprh.admis_prd_rt_hist_no = (SELECT Max(aprh2.admis_prd_rt_hist_no)
                                          FROM ADMISSION_PERIOD_RATE_HISTORY aprh2
                                          WHERE aprh2.injr_id = aprh.injr_id 
                                          AND aprh2.admis_no = aprh.admis_no 
                                          AND aprh2.admis_prd_no = aprh.admis_prd_no) 
            
         INNER JOIN ADMISSION_SCHEDULE_DETAIL asd
         ON asd.admis_sch_id = ap.admis_sch_id 
            
         INNER JOIN ADMISSION_SCHEDULE_DETAIL_AMT asda
         ON asda.cft_id = cft.cft_id
         AND asda.admis_sch_id = asd.admis_sch_id 
         AND asda.admis_sch_dtl_no = asd.admis_sch_dtl_no 
 ),
    
 cteq_2_union AS (
     SELECT injr_id             injr_id,
             bnft                bnft,
             ttdays             ttdays,
             tpdays             tpdays
     FROM cteq_2_1
        
     UNION ALL
        
     SELECT injr_id             injr_id,
             bnft                bnft,
             ttdays             ttdays,
             tpdays             tpdays
      FROM cteq_2_2
 ),
    
 cteq_2 AS (
     SELECT 
         injr_id             injr_id,
         SUM(ttdays)     ttdays,
         SUM(tpdays)     tpdays
     FROM cteq_2_union
     GROUP BY injr_id
     ORDER BY injr_id
 ),
    
 cteq_3_1 AS (
     SELECT cft.injr_id injr_id,
     TO_NUMBER(NVL(TO_CHAR(cft.cft_amt), 0)) cmppay,
     0 medpay,
     cft.cft_typ_cd,
     cft.cft_dt,
     vars.From_Dt,
     vars.reserve_date,
     cft.cft_id 
        
     FROM 
         cteq_1 cte1
            
         CROSS JOIN cteq_vars vars
            
         INNER JOIN CLAIM_FINANCIAL_TRAN cft 
         ON cft.injr_id = cte1.injr_id
         AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE' 
         AND Trunc(cft.cft_dt) >= vars.From_Dt 
         AND (
             (Trunc(cft.cft_dt) <= TRUNC(SYSDATE) AND vars.reserve_date IS NULL)
             OR 
             (Trunc(cft.cft_dt) <= TRUNC(vars.reserve_date) AND vars.reserve_date IS NOT NULL) 
         )
         AND (
             (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
             AND 
             (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
         )
 ),
    
 cteq_3_2 AS (
     SELECT 
         cft.injr_id injr_id,
         0 cmppay,
         TO_NUMBER(Nvl(TO_CHAR(cft.cft_amt), 0)) medpay
     FROM 
         cteq_1 cte1
            
         CROSS JOIN cteq_vars vars
            
         INNER JOIN CLAIM_FINANCIAL_TRAN cft
         ON cft.injr_id = cte1.injr_id
         AND UPPER(TRIM(cft.cft_typ_cd)) = 'MED_PAY_DUE' 
         AND Trunc(cft.cft_dt) >= vars.From_Dt 
         AND (
             (Trunc(cft.cft_dt) <= TRUNC(SYSDATE) AND vars.reserve_date IS NULL)
             OR 
             (Trunc(cft.cft_dt) <= TRUNC(vars.reserve_date) AND vars.reserve_date IS NOT NULL) 
         )
         AND (
             (cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
             AND 
             (cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
         )
 ),
 cteq_3_union
 AS
 (
     SELECT 
         injr_id injr_id,
         cmppay cmppay,
         medpay medpay
     FROM cteq_3_1
        
     UNION ALL
        
     SELECT 
         injr_id injr_id,
         cmppay cmppay,
         medpay medpay
     FROM cteq_3_2
 ),
    
 cteq_3 AS (
     SELECT 
         injr_id injr_id,
         SUM(cmppay) cmppay,
         SUM(medpay) medpay
      FROM cteq_3_union
      GROUP BY injr_id
 ),
    
 cteq_rsrv_1 AS (
     SELECT 
         cte1."Claim Number" "Claim Number",
         a.injr_id injr_id,
         SUM(a.rem_no_time_loss) NTLMed,
         SUM(a.rem_time_loss) TLMed,
         SUM(a.rem_comp_rsrv) TLRV,
         SUM(a.rem_sup_rsrv) SUPRV
            
     FROM    
         cteq_1 cte1 
            
         INNER JOIN REMAINING_RESERVES_HISTORY_TMP a
         ON a.injr_id = cte1.injr_id    
         AND a.rem_rsrv_hist_date = (SELECT MAX(b.rem_rsrv_hist_date) max_hist_date
                                     FROM REMAINING_RESERVES_HISTORY_TMP b
                                     WHERE b.injr_id = a.injr_id 
                                     AND (
                                         (TRUNC(b.rem_rsrv_hist_date) <= TRUNC(SYSDATE) AND cte1.reserve_date IS NULL) 
                                         OR
                                         (TRUNC(b.rem_rsrv_hist_date) <= cte1.reserve_date AND cte1.reserve_date IS NOT NULL) 
                                     ) 
         ) 
            
     GROUP BY    
         cte1."Claim Number",
         a.injr_id
            
     ORDER BY    cte1."Claim Number"
 ),
    
 cteq_rsrv AS (
     SELECT injr_id,
         GREATEST(0, NVL(cte1.TLRV, 0)) + GREATEST(0, NVL(cte1.SupRV, 0)) CMP_RV,
         GREATEST(0, NVL(cte1.NTLMed, 0)) + GREATEST(0, NVL(cte1.TLMed, 0)) MED_RV
      FROM cteq_rsrv_1 cte1
 ),
    
 cteq_paid AS (
     SELECT 
         cte1.injr_id        injr_id, 
         cte3.cmppay         cmppay,
         cter.CMP_RV         CMP_RV,
         cte3.medpay         medpay,
         cter.MED_RV         MED_RV
            
     FROM 
         cteq_1 cte1
            
         LEFT OUTER JOIN cteq_3 cte3
         ON cte3.injr_id = cte1.injr_id
            
         LEFT OUTER JOIN cteq_rsrv cter
         ON cter.injr_id = cte1.injr_id    
 ),
    
 cteq_outputfull1 AS (
     SELECT 
         cte1.injr_id injr_id,
         cte1."Claim Number" "Claim Number",
         cte1."IW Name" "IW Name",
         cte1."Occupation" "Occupation",
         cte1."Claimant Gender" "Claimant Gender",
         cte1."Claimant Age" "Claimant Age",
         cte1."Injury Date" "Injury Date",
         cte1."Injury Time" "Injury Time",
         cte1."Injury Description" "Injury Description",
         cte1."Claim Status" "Claim Status",
         cte1."Primary Body Part" "Primary Body Part",
         cte1."Body Side" "Body Side",
         cte1."Nature of Injury" "Nature of Injury",
         cte1."Injury Cause" "Injury Cause",
         cte1."Account Number" "Account Number",
         cte1."Employer" "Employer",
         cte1."WSI Notify Date" "WSI Notify Date",
         cte1."Years" "Years",
         cte1."Months" "Months",
         cte1."Days" "Days",
         cte1."Rate Class" "Rate Class",
         NVL(cte2.ttdays, 0) ttdays,
         NVL(cte2.tpdays, 0) tpdays,
         NVL(ctep.cmppay, 0) cmppay,
         NVL(ctep.CMP_RV, 0) CMP_RV,
         NVL(ctep.medpay, 0) medpay,
         NVL(ctep.MED_RV, 0) MED_RV,
         cte1.vars_From_Dt vars_From_Dt,
         cte1.vars_To_Dt vars_To_Dt,
         cte1.vars_Paid_From vars_Paid_From,
         cte1.vars_Paid_To vars_Paid_To
      FROM 
         cteq_1 cte1
            
         LEFT OUTER JOIN cteq_2 cte2
         ON cte1.injr_id = cte2.injr_id
            
         LEFT OUTER JOIN cteq_paid ctep
         ON ctep.injr_id = cte1.injr_id
 )
    
 SELECT 
     "Claim Number"             "Claim Number",     
     "IW Name" "Claimant Name",
     "Claimant Age" "Claimant Age",
     "Claimant Gender" "Claimant Gender",
     "Occupation" "Occupation",
     "Years" "Employment Years",
     "Months" "Employment Months",
     "Days" "Employment Days",
     "WSI Notify Date" "WSI Notify Date",
     "Injury Date" "Injury Date",
     "Injury Time" "Injury Time",
     "Injury Description" "Injury Description",
     "Claim Status" "Claim Status",     
     "Account Number" "Account Number",            
     "Employer" "Employer",
     "Rate Class" "Rate Class",     
     "Primary Body Part" "Primary Body Part", 
     "Body Side" "Body Side",
     "Injury Cause" "Injury Cause",
     "Nature of Injury" "Nature of Injury",
     cmppay "Comp Paid",         
     medpay "Med Paid",         
     CMP_RV "Comp Reserve",     
     MED_RV "Med Reserve",     
     cmppay + medpay + CMP_RV + MED_RV "Total Incurred", 
     ttdays + tpdays "Total TL Days"
        
 FROM cteq_outputfull1
    
 ORDER BY "Claim Number"
    
 /**/
sql-server-transact-sqlsql-server-reporting-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Can you remove all or some of UPPER and TRIM from conditions? Usually, UPPER is not required if the database or collation is case-insensitive.

0 Votes 0 ·

Hi @SteveGriffin-8463,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
IgorGelin-0063 avatar image
0 Votes"
IgorGelin-0063 answered

Hi,

Look for missing indexes.

Thanks.

HTH

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

SELECT TO_DATE

That's Oracle P/SQL, isn't it?
Then you posted to a wrong forum, thei one is for Microsoftr SQL Server.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SteveGriffin-8463,

Welcome to Microsoft Q&A!

After checking, your report query does not seem to come from SQL Server.

Could you please let us know where does it come from and what database language it is?

If it is from MySQL, Oracle or other language, we recommend you to post this question in the corresponding forum and you will get more professional help from many experts.

If it is from exactly SQL Server, we recommend you to post the CREATE TABLE statements for your tables together with INSERT statements with sample data, the size of your datasets ,and the execution plan so that we could check further.

Besides, you could have a try to refer following and check whether it is helpful:

  • Create necessary indexes or remove extra indexes.

  • Update statistics.

  • Check the physics factors of the server, like disk and memory.

  • Check whether there is any blocking ,deadlock or submitted transaction.

  • Limit the complexity of the sentence as much as possible.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.