case when rx_formula in ('approved','unrestricted') then '1' else'0' end as positive ,
case when rx_formula not in ('approved','unrestricted') then '1' else'0' end as negative
group by 1,2
It's very urgent reqirement,could you please help us....
Thanks in advance...
1. GROUP records FROM MISSION_CASE_HIST based ON the same MISSION_CASE_ID , MCID AND MBR_CASE_ID ORDER BY MISSION_CASE_ENTRY_DTM
a. EACH record would have TO be examined IN this ORDER:
b. Episode/record 1 :The very FIRST record WHERE MISSION_CASE_HIST .CP_STTS_CD = 'OPEN' would be the FIRST episode FOR that GROUPING (USE MISSION_CASE_HIST.MISSION_CASE_ENTRY_DTM FOR PM_STRT_DT)
c. NEXT Episode / record FOR the same GROUPING :
EACH subsequent record FOR the same GROUP WITH MISSION_CASE_HIST .CP_STTS_CD = 'OPEN'
(AND ONLY IF this record FOLLOWING the record WITH MISSION_CASE_HIST .CP_STTS_CD <> 'OPEN' ) would also CREATE an episode(USE CAST(MISSION_CASE_HIST.MISSION_CASE_ENTRY_DTM AS DATE) FOR PM_STRT_DT)