0

User requirement:- 2 policies in each product code with Highest (Maximum) SUM INSURED How do I get 2 policies in each product code with Highest (Maximum) SUM INSURED

SELECT
    PROD.MV_PREMIUM_REGISTER.T_DATE_DESC,
    PROD.MV_PREMIUM_REGISTER.P_POLICY_NUMBER,
    PROD.MV_PREMIUM_REGISTER.P_POLICY_STATUS,
    PROD.MV_PREMIUM_REGISTER.VERSION,
    PROD.MV_PREMIUM_REGISTER.P_RISK_INC_DATE,
    PROD.MV_PREMIUM_REGISTER.P_OFFICE_LOC_ID,
    PROD.MV_PREMIUM_REGISTER.LOCATION_DESC,
    PROD.MV_PREMIUM_REGISTER.ZONE_DESC,
    PROD.MV_PREMIUM_REGISTER.I_IMD_DESC,
    PROD.MV_PREMIUM_REGISTER.P_SUB_IMD,
    PROD.MV_PREMIUM_REGISTER.P_PRODUCT_ID,
    PROD.MV_PREMIUM_REGISTER.P_RISK_EXPIRY_DATE,
    PROD.MV_PREMIUM_REGISTER.P_GC_PLAN,
    PROD.MV_PREMIUM_REGISTER.IMD_CHANNEL,
    PROD.MV_PREMIUM_REGISTER.P_PRODUCT_DESC,
    (PROD.MV_PREMIUM_REGISTER.SUM_INSURED) AS SUM_INSURED,
    (PROD.MV_PREMIUM_REGISTER.GROSS_PREMIUM) AS GROSS_PREMIUM,
    (PROD.MV_PREMIUM_REGISTER.STAMP_DUTY) AS STAMP_DUTY
FROM
    PROD.MV_PREMIUM_REGISTER
WHERE
    T_DATE_DESC BETWEEN '27-FEB-2022' AND '28-FEB-2022'
    AND VERSION NOT LIKE '%E%'
    AND LT_POLICY_YEAR = 1;
MT0
  • 143,790
  • 11
  • 59
  • 117
Axay
  • 7
  • 2
  • what is product code in table MV_PREMIUM_REGISTER? – Sai Patil Mar 08 '22 at 06:08
  • P_PRODUCT_ID is product code – Axay Mar 08 '22 at 06:22
  • What is the data type of T_DATE_DESC? If it is _not_ a DATE, then you have a serious design flaw. In any event, you are trying to compare it to a character string. And that in a format that doesn't collate in date order. As a string '27-APR-2022' will come _before_ '27-FEB-2022'. If T_DATE_DESC is, as it should be, a DATE, then you are forcing an implied TO_DATE conversion, that may or may not work as you expect . . . if at all. – EdStevens Mar 08 '22 at 15:07

1 Answers1

1
SELECT P_PRODUCT_ID,P_POLICY_NUMBER,SUM_INSURED from(
SELECT
PROD.MV_PREMIUM_REGISTER.P_PRODUCT_ID,
PROD.MV_PREMIUM_REGISTER.P_POLICY_NUMBER,
(PROD.MV_PREMIUM_REGISTER.SUM_INSURED) AS SUM_INSURED,
RANK() OVER(PARTITION BY P_PRODUCT_ID ORDER BY SUM_INSURED DESC) AS 
RANK_COUNT
FROM
PROD.MV_PREMIUM_REGISTER
WHERE
T_DATE_DESC BETWEEN '27-FEB-2022' AND '28-FEB-2022'
AND VERSION NOT LIKE '%E%'
AND LT_POLICY_YEAR = 1) MVP where MVP.RANK_COUNT <3;
Anand Satheesh
  • 94
  • 5