I am struggling with implementing two ‘flag’ columns to show whether an applicant has been through a process called ‘Clearing’ or not, and whether that applicant had a prior standard application to University – and then went through Clearing. These two flags are called ‘In Clearing?’ and ‘Prior standard application?’

I can tell whether an applicant has been through Clearing based on two columns in two tables: ‘Decision Desc’ = ‘UA Clearing’ (Table ‘Sequence’), and their ‘Choice Number’ = 7 (Table ‘Final’). If these conditions were true, they would have a ‘Yes’ in the ‘In Clearing?’ column.

I can tell whether an Clearing applicant also had a prior standard application because they will have another row with ‘Choice Number’ = 1-5, on top of the existing ‘UA Clearing’ and ‘Choice Number’ = 7 in another row. If these conditions were true, they would have a ‘Yes’ in the ‘Prior standard application?’ column.

Each applicant/row has the unique identifier of ‘ID’. The only applicants that should be effected by this process are ones with ‘Choice Number’ 7.

To summarise:
‘In Clearing?’ = ‘Yes’ if Decision Desc = ‘UA Clearing’ and ‘Choice Number’ = 7.
‘Prior standard application?’ = ‘Yes’ if ‘In Clearing’ = ‘Yes’ and ‘Choice Number’ = 1-5.
Only add to ‘Yes’ to rows with ‘Choice Number’ = 7.

The problem I cannot solve: making it so the only rows that get affected by this are ones with ‘Choice Number’ 7.

For example, this is what I am aiming for:

As you can see, U15 has ‘Yes’ in both because it has one row with ‘Decision Desc’ = ‘UA Clearing’ and ‘Choice Number’ = 7, and another row with Choice Number ‘1-5’. HOWEVER, ‘In Clearing?’ and ‘Prior standard application?’ should only have ‘Yes’ on the row that has Choice Number ‘7’.

This is the code I’ve tried:

DROP TABLE IF EXISTS SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing;

-- Copy data of BOXI source into new file for Clearing content
SELECT *
INTO SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing
FROM SA_ConfirmationAndClearing_CurrentYearDecisionSequence

-- Add Flag columns to Sequence_Clearing show whether an applicant is in Clearing or not, and if they have a standard application prior to clearing.
-- Done repeatedly because this table is dropped in this script
ALTER TABLE SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing 
ADD [In Clearing?] varchar(255),
    [Prior standard application?] varchar (255)

-- Create temporary table to track UCAS IDs with different application scenarios
DROP TABLE IF EXISTS #UCAS_ID_to_Choice_Number;

-- Classify UCAS IDs based on their choice numbers and decision desc
SELECT 
    Final.[UCAS Id],
    CASE
        WHEN COUNT(DISTINCT CASE WHEN Final.[Choice Number] = '7' THEN 1 ELSE NULL END) > 0
             AND COUNT(DISTINCT CASE WHEN Final.[Choice Number] IN ('1', '2', '3', '4', '5') THEN 1 ELSE NULL END) > 0 THEN '1-5 and 7 - Both'
        WHEN COUNT(DISTINCT CASE WHEN Final.[Choice Number] = '7' THEN 1 ELSE NULL END) > 0 THEN 'Only 7 - Clearing'
        ELSE '1-5 - Nulls'
    END as [Choice Number Type]
INTO #UCAS_ID_to_Choice_Number
FROM [REP_ConfirmationAndClearing_Final] as Final
JOIN SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing as Sequence
    ON Final.[UCAS Id] = Sequence.[UCAS Id]
WHERE Sequence.[Decision Desc] = 'UA Clearing'
GROUP BY Final.[UCAS Id];

-- Update the _Clearing table based on the temp table
UPDATE Sequence
SET
    [In Clearing?] = CASE
                        WHEN UCASTempTable.[Choice Number Type] IN ('Only 7 - Clearing', '1-5 and 7 - Both') THEN 'Yes'
                        ELSE NULL
                     END,
    [Prior standard application?] = CASE
                                        WHEN UCASTempTable.[Choice Number Type] = '1-5 and 7 - Both' THEN 'Yes'
                                        ELSE NULL
                                     END
FROM SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing as Sequence
JOIN #UCAS_ID_to_Choice_Number as UCASTempTable
    ON Sequence.[UCAS Id] = UCASTempTable.[UCAS Id]
WHERE Sequence.[Decision Desc] = 'UA Clearing';

-- Update the Final table
UPDATE Final
SET 
    Final.[In Clearing?] = COALESCE(Clearing.[In Clearing?], NULL),
    Final.[Prior standard application?] = COALESCE(Clearing.[Prior standard application?], NULL)
FROM [REP_ConfirmationAndClearing_Final] AS Final
INNER JOIN [SA_ConfirmationAndClearing_CurrentYearDecisionSequence_Clearing] AS Clearing
ON Final.[UCAS Id] = Clearing.[UCAS Id]
WHERE Clearing.[Decision Desc] = 'UA Clearing';

This script is making the error of adding ‘Yes’ to the rows with ‘Choice Number’ = 1-5, when it should be the ones with ‘Choice Number’ = 7.

Example with error:

The error here is that both rows have ‘Yes’ in the ‘In Clearing?’ and ‘Prior standard application?’ columns, when it should only be the one with ‘Choice Number’ 7.