Student Visa Approval Process

Table: STUDENT_MST

VISA_APPROVED:
    'P': Visa Approval is Pending
    'Y': Visa is Approved
    'N': Visa is not Approved

UNIVERSITY_APPROVED:
    'P': University Approval is Pending
    'Y': University has approved the Student
    'N': University has rejected the Student

Table: FILE_MST

FILE_PROCESSED:
    'N': File not processed
    'Y': File Processed 

I want to fetch all the records where a student has applied more than once [COUNT(studntMst.STUDENT_ID) > 1] and only their Visa got first rejected and then approved [studntMst.VISA_APPROVED in ('N','Y')] and the file is processed [fileMst.FILE_PROCESSED = 'Y']

Following query is not returning any output.

SELECT 
studntMst.STUDENT_MST_ID,
studntMst.STUDENT_ID,
studntMst.VISA_APPROVED,
studntMst.UNIVERSITY_APPROVED,
fileMst.STUDENT_FILE_ID,
fileMst.FILE_PROCESSED
FROM 
STUDENT_MST studntMst
JOIN FILE_MST fileMst on fileMst.STUDENT_FILE_ID = studntMst.STUDENT_FILE_ID
and studntMst.VISA_APPROVED in ('N','Y')
and fileMst.FILE_PROCESSED = 'Y'
GROUP BY
studntMst.STUDENT_MST_ID,
studntMst.STUDENT_ID,
studntMst.VISA_APPROVED,
studntMst.UNIVERSITY_APPROVED,
fileMst.STUDENT_FILE_ID,
fileMst.FILE_PROCESSED
HAVING COUNT(studntMst.STUDENT_ID) > 1
ORDER BY studntMst.STUDENT_MST_ID desc;

Ideally it should return the following.

Queries to create table and insert data.

CodePudding user response：

SELECT 
*
FROM 
STUDENT_MST studntMst
JOIN FILE_MST fileMst on fileMst.STUDENT_FILE_ID = studntMst.STUDENT_FILE_ID
and studntMst.STUDENT_ID in (
    SELECT 
    studntMst.STUDENT_ID
    FROM 
    STUDENT_MST studntMst
    JOIN FILE_MST fileMst on fileMst.STUDENT_FILE_ID = studntMst.STUDENT_FILE_ID
    and studntMst.VISA_APPROVED in ('N','Y')
    and fileMst.FILE_PROCESSED = 'Y'
    GROUP BY
    studntMst.STUDENT_ID
    HAVING COUNT(studntMst.STUDENT_ID) > 1
)
ORDER BY studntMst.STUDENT_MST_ID desc;

CodePudding user response：

You can follow up with three steps:

• join the "STUDENT_MST" table with the "FILE_MST" table on matching "STUDENT_FILE_ID" field and filtering out non-processed files (f.FILE_PROCESSED <> 'Y')
• use two SUM window functions, one to identify the amount of rejected visas, another to identify the amount of approved visas
• filter out rows that have these window function values less than 1 (which means that the student has had their visa accepted and rejected at least once)

WITH cte AS (
    SELECT s.*, 
           SUM(CASE WHEN VISA_APPROVED = 'Y' THEN 1 END) OVER(PARTITION BY STUDENT_ID) AS y,
           SUM(CASE WHEN VISA_APPROVED = 'N' THEN 1 END) OVER(PARTITION BY STUDENT_ID) AS n
    FROM       STUDENT_MST s
    INNER JOIN FILE_MST    f
            ON s.STUDENT_FILE_ID = f.STUDENT_FILE_ID
           AND f.FILE_PROCESSED = 'Y'
)
SELECT STUDENT_MST_ID,
       STUDENT_ID,
       STUDENT_FILE_ID,
       VISA_APPROVED,
       UNIVERSITY_APPROVED
FROM cte
WHERE y >= 1 AND n >= 1

Check the demo here.

Assumption: rejected visas come always before accepted visas. If this is not the case, you need an additional LAST_VALUE window function which gathers the last value for the visa state, partitioned by each student. Eventually filter out those students whose last visa state is not 'Y'.