I am comapring accid from previous month and current month. IF accid existed in previous month, but does not exist in the current month THEN 'Closed'. ELSE IF accid did not exist last month but exists in the current month THEN 'New' ELSE 'Old'"
The following query is not working pls. advise! select CMN.accid as current_month,PMN.accid as prev_month, case when select PMN.accid from prev_month_table PMN minus select CMN.accid from current_month_table CMN then 'Closed' when select CMN.accid from current_month_table CMN minus select accid from prev_month_table then 'New' else 'Old' end as acc_status from current_month_table, prev_month_table
sel Coalesce ( CMN.accid ,PMN.Accid) as ACCID, CASE when CMN.accid is null and PMN.Accid is not null then 'Closed' when CMN.accid is not null and PMN.Accid is null then 'NEW' when CMN.accid is not null and PMN.Accid is not null then 'OLD' END as acc_status from CMN full outer join PMN on CMN.accid = PMN.accid ;