Need Help in Selection -

Database

Need Help in Selection -

 
2 REPLIES

Re: Need Help in Selection -

I have an inner query which is something like below - 

SELECT C.mstr_tvr_prg_card_id,

CASE

WHEN MAX(D.TRAN_DT) < CURRENT_DATE -90 then 'Y' 

ELSE 'N'  

end AS LAPSE_IND_1,

case when LAPSE_IND_1 ='N' and MAX(D.TRAN_DT) is null then 'Y'

 when LAPSE_IND_1 ='Y' then 'Y'

else 'N'

END as LAPSE_IND_2

from

 EDW_DEV2_VIEWS.TVR_PRG_PRTY C

 LEFT OUTER JOIN SLS_TRAN    D

on C.tvr_prg_card_id = D.tvr_prg_card_id

AND C.tvr_prg_card_id IS NOT NULL

group by 1

The result is something like below --

MST_TVR_CARD St.lapse_ind st.papse_ind_2

1                           Y              Y

1                           N             Y

1                           N             N

1                           Y              Y

2                           N             Y

2                           Y              N

2                           N              Y

I want to implement a logic where if there is a single lapse_ind_2 = 'N' for any of the mstr_tvr_card all values should become 'N'.

This will be a part of my subquery .

Any suggestions ?

Enthusiast

Re: Need Help in Selection -

SELECT MST_TVR_CARD,St.lapse_ind,MIN(st.papse_ind_2) OVER PARTITION BY (
MST_TVR_CARD ORDER BY MST_TVR_CARD) FROM TABLENAME