I want to derive column MSP_ADULT and MSP_CHILD based on the LATEST date record ADULT_AMT to be in MSP_ADULT and CHILD_AMT to be in MSP_CHILD column. I want to my out like below. Here is the code I am running, but it is not working. Answer So you want the last values of those conditional MAX's. Try FIRST_VALUE with a

How to display Max Date amount in another column?

I want to derive column MSP_ADULT and MSP_CHILD based on the LATEST date record ADULT_AMT to be in MSP_ADULT and CHILD_AMT to be in MSP_CHILD column.

I want to my out like below.

END_DATE    ADULT_AMT  CHILD_AMT  MSP_ADULT  MSP_CHILD
09/01/2017   100          50           180       80
10/01/2018   200          100          180       80 
04/05/2019   300          90           180       80
08/20/2019   180          80           180       80

Here is the code I am running, but it is not working.

SELECT  
    AL1.END_DATE as BROCHURE_EFFECTIVE_END_DATE, 
    PORT_CODE,
    AL7.PRODUCT_LEGACY_CODE as COMPONENT_CODE, 
    AL3.PRICE_GUEST_AGE_GROUP ,
    MAX(CASE WHEN AL3.PRICE_GUEST_AGE_GROUP = 'ADULT' THEN AL3.PRICE_AMOUNT ELSE 0 END) ADULT_AMT, 
    MAX(CASE WHEN AL3.PRICE_GUEST_AGE_GROUP = 'CHILD' THEN AL3.PRICE_AMOUNT ELSE 0 END) CHILD_AMT,     
   MAX(CASE WHEN AL3.PRICE_GUEST_AGE_GROUP = 'ADULT' THEN AL3.PRICE_AMOUNT ELSE 0 END) 
            OVER (PARTITION BY PORT_CODE, AL7.PRODUCT_LEGACY_CODE --order by AL1.END_DATE desc
    )AS MSP_ADULT,
   MAX(CASE WHEN AL3.PRICE_GUEST_AGE_GROUP = 'CHILD' THEN AL3.PRICE_AMOUNT ELSE 0 END) 
            OVER (PARTITION BY PORT_CODE, AL7.PRODUCT_LEGACY_CODE order by AL1.END_DATE desc  ) AS MSP_child       
FROM RATE_PLAN AL1
inner join PRICE AL3
on (AL3.RATE_PLAN_SK=AL1.RATE_PLAN_SK and AL3.rate_plan_sk <>-1 )
Inner join PRODUCT_VARIANT AL7
ON (AL3.PRODUCT_CODE = AL7.PRODUCT_LEGACY_CODE and AL7.CATALOG_VERSION='Online')
INNER JOIN PRODUCT_OFFERING AL14
ON (AL14.PRODUCT_CODE = AL7.PRODUCT_LEGACY_CODE and AL7.CATALOG_VERSION='Online')
inner join PORT 
on (AL7.FULFILLMENT_LOCATION = PORT_CODE)
where  TO_CHAR(AL1.END_DATE,'YYYY') >= TO_CHAR(SYSDATE,'YYYY')
  and AL3.use_for_pricing_flag is not null
and port_code='HKT' and AL7.product_LEGACY_code='PK83'
GROUP BY        
        PORT_CODE,
        PRICE_GUEST_AGE_GROUP,
        AL7.PRODUCT_LEGACY_CODE,
        AL1.END_DATE

)

Answer

So you want the last values of those conditional MAX’s.

Try FIRST_VALUE with a descending order.

...
FIRST_VALUE(ADULT_AMT) 
        OVER (PARTITION BY PORT_CODE, COMPONENT_CODE ORDER BY BROCHURE_EFFECTIVE_END_DATE DESC) AS MSP_ADULT,
FIRST_VALUE(CHILD_AMT)
        OVER (PARTITION BY PORT_CODE, COMPONENT_CODE ORDER BY BROCHURE_EFFECTIVE_END_DATE DESC) AS MSP_CHILD       
...

Note that there’s also the LAST_VALUE window function, but that one can sometimes be misleading.

Advertisement

Answer