oracle - sql analytical function usage-CodePudding

I have a query with output as below:

select c.case_id caseId, c.memberId memberId, c.last_name lastName, c.first_name firstName, lkp_alt.descr altShowCauseAuthority, 
 'Active' as caseType,
 listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
 from cases c join lkP_alt_show_cause_authority lkp_alt
 on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
 and c.case_type = 'P'
 JOIN (SELECT case_id, 
               case_status_id,
               COUNT(DECODE(case_status_id, 2, 1, NULL)) OVER (PARTITION BY case_id) AS IS_CLOSED
        FROM case_status) cs ON cs.case_id = c.case_id AND cs.is_closed = 0
 join lkp_case_status lkp_cs
 on lkp_cs.id = cs.case_status_id 
 where (c.created_by = 1 and c.assigned_to is null)
 or (c.assigned_to = 1) and c.delete_date is null
 group by c.case_id, c.memberId, c.last_name, c.first_name, lkp_alt.descr, c.case_type;

Output:

caseId  memberId    lastName    firstName   altShow caseType caseStatus
101     1365385501  WWW         test        MARFORK Active   Active ,Closed ,EO         
102     1501534761  AAA         test        MARFORK Active   IGMC,JPAS                  
110     1572158078  CCC         test        I MEF   Active   EO,EEO

Case_status is as follows:

case_id case_status_id case_status_date
101     1               17-OCT-21
101     2//Closed       17-NOV-19
101     3               17-OCT-21
102     4               17-OCT-21
102     5               17-OCT-21
110     8               17-OCT-21
110     9               17-OCT-21

Now I want my query to display all cases except closed case with case status date less than 365 days from current date, which means i shouldnt display case 101 since it has a case status closed and date is 2 years past. Any suggestions please.

CodePudding user response：

If you want don't display all records when case_id = 101 then

SELECT * FROM CASES 
WHERE 
CASE_ID NOT IN
    (
        SELECT CASE_ID FROM CASES 
        WHERE 
            CASE_STATUS_ID = 2 AND 
            MONTHS_BETWEEN(SYSDATE, CASE_STATUS_DATE)>12
    )

-- both queries get same results, but this query more performancing     
SELECT C1.* FROM CASES C1 
LEFT JOIN (
            SELECT CASE_ID FROM CASES 
            WHERE 
                CASE_STATUS_ID = 2 AND 
                MONTHS_BETWEEN(SYSDATE, CASE_STATUS_DATE)>12
          ) C2 ON C1.CASE_ID = C2.CASE_ID 
WHERE C2.CASE_ID IS NULL

If you want don't display only this record which case_id = 101 and case_status_id=2 then

SELECT * FROM CASES 
WHERE
    NOT (CASE_STATUS_ID = 2 AND 
    MONTHS_BETWEEN(SYSDATE, CASE_STATUS_DATE)>12)

If you want to filter for days you can use this condition: CAST((SYSDATE - CASE_STATUS_DATE) AS INTEGER) > 365