3 Replies Latest reply on Sep 16, 2013 9:14 AM by Kevin Herbert

    replace rawsqlagg_int with table functions?

    Kevin Herbert

      How can i do this without custom sql(another data connection or rawsqlagg?

       

      RAWSQLAGG_INT(

      "(SELECT SUM(CS_CLIENT_COVERED_COUNT)

      FROM

          (  -- TWO MONTHS AGO FROM DATE AND SUBPROGRAM

           SELECT f.CS_CLIENT_COVERED_COUNT

           FROM REPORT_LDM.LDM_F_CS_CLIENT_COV_MONTH F

           INNER JOIN REPORT_LDM.LDM_D_CASE C

              ON F.CASE_PK = C.CASE_PK

           INNER JOIN REPORT_LDM.LDM_D_TIME T

              ON F.COVERAGE_DATE_PK = t.TIME_PK

           INNER JOIN REPORT_LDM.LDM_D_SUBPROGRAM S

              ON F.SUBPROGRAM_PK = S.SUBPROGRAM_PK

           WHERE T.THEDATE = add_months(TRUNC(TO_DATE(%1), 'MM'), -3 )

           and S.SUBPROGRAM_NAME = %2

           and exists

              (   -- NEW APP FOR MONTH OF DATE AND SUBPROGRAM

                  SELECT 'x'  

                   FROM REPORT_LDM.LDM_F_CS_CLIENT_COV_MONTH F1

                   INNER JOIN REPORT_LDM.LDM_D_CASE C1

                      ON F1.CASE_PK = C1.CASE_PK

                   INNER JOIN REPORT_LDM.LDM_D_TIME T1

                      ON F1.COVERAGE_DATE_PK = t1.TIME_PK

                   INNER JOIN REPORT_LDM.LDM_D_SUBPROGRAM S1

                      ON F1.SUBPROGRAM_PK = S1.SUBPROGRAM_PK

                   WHERE T1.THEDATE = TO_DATE(%1)

                      AND S1.SUBPROGRAM_NAME = %2

                      AND F1.CS_APPLICATION_COUNT = 1

                      AND C.CASE_NUMBER = C1.CASE_NUMBER

                      AND F.CLIENT_PK = F1.CLIENT_PK        

              )

                   and not exists  -- ONE MONTH AGO FROM DATE AND SUBPROGRAM

              (

                  SELECT 'x'

                       FROM REPORT_LDM.LDM_F_CS_CLIENT_COV_MONTH F2

                       INNER JOIN REPORT_LDM.LDM_D_CASE C2

                          ON F2.CASE_PK = C2.CASE_PK

                       INNER JOIN REPORT_LDM.LDM_D_TIME T2

                          ON F2.COVERAGE_DATE_PK = t2.TIME_PK

                       INNER JOIN REPORT_LDM.LDM_D_SUBPROGRAM S2

                          ON F2.SUBPROGRAM_PK = S2.SUBPROGRAM_PK

                       WHERE T2.THEDATE = add_months(TRUNC(TO_DATE(%1), 'MM'), -2 )

                       and S2.SUBPROGRAM_NAME = %2

                       and f2.CS_CLIENT_COVERED_COUNT = 1

                       AND C.CASE_NUMBER = C2.CASE_NUMBER

                       AND F.CLIENT_PK = F2.CLIENT_PK      

              )

           and not exists  -- ONE MONTH AGO FROM DATE AND SUBPROGRAM

              (

                  SELECT 'x'

                       FROM REPORT_LDM.LDM_F_CS_CLIENT_COV_MONTH F2

                       INNER JOIN REPORT_LDM.LDM_D_CASE C2

                          ON F2.CASE_PK = C2.CASE_PK

                       INNER JOIN REPORT_LDM.LDM_D_TIME T2

                          ON F2.COVERAGE_DATE_PK = t2.TIME_PK

                       INNER JOIN REPORT_LDM.LDM_D_SUBPROGRAM S2

                          ON F2.SUBPROGRAM_PK = S2.SUBPROGRAM_PK

                       WHERE T2.THEDATE = add_months(TRUNC(TO_DATE(%1), 'MM'), -1 )

                       and S2.SUBPROGRAM_NAME = %2

                       and f2.CS_CLIENT_COVERED_COUNT = 1

                       AND C.CASE_NUMBER = C2.CASE_NUMBER

                       AND F.CLIENT_PK = F2.CLIENT_PK      

              )

             

              )

          )"

      ,[zp_Coverage_Date A]

      ,[zp_Subprogram A]

      )