Please assist me to get the record of the last month of Claim Payment Transaction table ?

[suvechha_b]

Spoiler

Hi All,

Can anyone help me out with the view -

Please assist me in the below query. My max RB.RB_CALENDAR_PERIOD is '2019-04-01 00:00:00.000' But my max date for table DWUQV_CLAIM_PAYMENT_TRANSACTION is '2019-03-01 00:00:00.000' . How can I get the SUM(GROSS_CLAIMS_PAID) for the period of 

'2019-04-01 00:00:00.000' or less which is '2019-03-01 00:00:00.000'. in my ultimate view . Below is the view script.

SELECT

  CONCAT(
    LATEST_CLAIMS.RECORD_SOURCE_GRP_ID,'.',LATEST_CLAIMS.RECORD_SOURCE_GM_ID,'|',
    LATEST_CLAIMS.INSURER,'|',
    LATEST_CLAIMS.PRODUCT_CODE,'|',
    LATEST_CLAIMS.POLICY_NUMBER,'|',
    LATEST_CLAIMS.CLAIM_NUMBER
  ) CLAIM_WEAKKEY,
  CONVERT(date,LATEST_CLAIMS.DATE_OF_LOSS) DATE_OF_LOSS,
  SUM(CPT.GROSS_CLAIMS_PAID) TOTAL_GROSS_CLAIMS_PAID

FROM
  --region LATEST_CLAIMS
  (
    SELECT
      RECORD_SOURCE_GRP_ID,
      RECORD_SOURCE_GM_ID,
      INSURER,
      PRODUCT_CODE,
      POLICY_NUMBER,
      CLAIM_NUMBER,
      DATE_OF_LOSS,
      RECORD_BATCH_GRP_ID,
      RECORD_BATCH_GM_ID,
      RECORD_BATCH_GROUP_GRP_ID,
      RECORD_BATCH_GROUP_GM_ID,
      /**/RB_CALENDAR_PERIOD
    FROM
      --region GROUPED_CLAIMS
      (
        SELECT
          RB.RECORD_SOURCE_GRP_ID,
          RB.RECORD_SOURCE_GM_ID,
          C.INSURER,
          C.PRODUCT_CODE,
          C.POLICY_NUMBER,
          C.CLAIM_NUMBER,
          C.DATE_OF_LOSS,
          C.RECORD_BATCH_GRP_ID,
          C.RECORD_BATCH_GM_ID,
          RB.RECORD_BATCH_GROUP_GRP_ID,
          RB.RECORD_BATCH_GROUP_GM_ID,
          /**/RB.RB_CALENDAR_PERIOD,
          ROW_NUMBER() OVER (
            PARTITION BY C.INSURER, C.PRODUCT_CODE, C.CLAIM_NUMBER 
            ORDER BY C.INSURER, C.PRODUCT_CODE, C.CLAIM_NUMBER, RB.RB_CALENDAR_PERIOD DESC
          ) ROW_NUM
        FROM
          DWU_CLAIM C
        INNER JOIN
          DWV_RECORD_BATCH RB
        ON
          RB.RECORD_BATCH_GRP_ID = C.RECORD_BATCH_GRP_ID AND
          RB.RECORD_BATCH_GM_ID  = C.RECORD_BATCH_GM_ID
      ) GROUPED_CLAIMS
      --endregion
    WHERE
      ROW_NUM =1
  ) LATEST_CLAIMS
  --endregion
INNER LOOP JOIN  
  DW_RECORD_BATCH OTHER_RB
ON
  OTHER_RB.RECORD_BATCH_GROUP_GRP_ID =  LATEST_CLAIMS.RECORD_BATCH_GROUP_GRP_ID AND
  OTHER_RB.RECORD_BATCH_GROUP_GM_ID  =  LATEST_CLAIMS.RECORD_BATCH_GROUP_GM_ID AND
  OTHER_RB.RECORD_BATCH_GRP_ID       =  LATEST_CLAIMS.RECORD_BATCH_GRP_ID AND
  OTHER_RB.RECORD_BATCH_GM_ID        <> LATEST_CLAIMS.RECORD_BATCH_GM_ID 
  LEFT OUTER LOOP JOIN
  DWU_CLAIM_PAYMENT_TRANSACTION CPT
ON
  CPT.INSURER             = LATEST_CLAIMS.INSURER AND
  CPT.PRODUCT_CODE        = LATEST_CLAIMS.PRODUCT_CODE AND
  CPT.CLAIM_NUMBER        = LATEST_CLAIMS.CLAIM_NUMBER AND
  CPT.RECORD_BATCH_GRP_ID = OTHER_RB.RECORD_BATCH_GRP_ID AND
  CPT.RECORD_BATCH_GM_ID  = OTHER_RB.RECORD_BATCH_GM_ID

GROUP BY
  LATEST_CLAIMS.RECORD_SOURCE_GRP_ID,
  LATEST_CLAIMS.RECORD_SOURCE_GM_ID,
  LATEST_CLAIMS.INSURER,
  LATEST_CLAIMS.PRODUCT_CODE,
  LATEST_CLAIMS.POLICY_NUMBER,
  LATEST_CLAIMS.CLAIM_NUMBER,
  LATEST_CLAIMS.DATE_OF_LOSS

0

I will try to explain.

My Claim Table has max RB_CALENDAR_PERIOD - '2019-04-01 00:00:00.000'

But , in my transaction table DWUQV_CLAIM_PAYMENT_TRANSACTION, I need to fetch the record of RB_CALENDAR_PERIOD -'2019-03-01 00:00:00.000'

My requirement is to get the SUM(GROSS_CLAIMS_PAID) for the period of 

'2019-04-01 00:00:00.000' or less which is '2019-03-01 00:00:00.000'. in my ultimate view 

If possible , please kindly assist.

[Anil_Babu_Samineni]

Try this?

SUM({<RECORD_BATCH_GROUP_GRP_ID = {"=IMPORT_MONTH>=$(=Max(IMPORT_MONTH)) and RB_CALENDAR_PERIOD <= $(=Max(RB_CALENDAR_PERIOD))"}>} GROSS_CLAIMS_PAID)