SQLTeam.com | Weblogs | Forums

Joining two tables to see if any data transfer or stay with same value in a field

Hi below is my question:

There are 2 data sets HEF_2021Q1 and HEF_2021Q2, each contains 4 fields: acct_id, stage, balance, and ecl. The field “stage” contains 2 values, either 1 or 2. Identified the accounts that remained in the portfolio (i.e. common accounts in both data sets provided), and populate the balance distribution of these accounts by their movement (4 possibilities: from stage1 to stage2, or from stage2 to stage1, or stayed in stage1, or stayed in stage2.)

I have the code written here. Can you help me to see I have written correctly? Thanks!

Select sum(from_stage_1_to_stage_2), sum(from_stage_2_to_stage_1),sum(stayed_in_stage_1),sum(stayed_in_stage_2)
from (select distinct a.acct_id, a.stage as Q1_stage, b.stage as Q2_stage,
a.balance as Q1_balance, b.balance as Q2_balance,
case when Q1_Stage <Q2_Stage then Q2_balance else 0 end as from_stage_1_to_stage_2,
case when Q1_Stage >Q2_Stage then Q2_balance else 0 end as from_stage_2_to_stage_1,
case when (Q1_Stage =Q2_Stage and Q1_Stage ='1') then Q2_balance else 0 end as stayed_in_stage_1,
case when (Q1_Stage =Q2_Stage and Q1_Stage ='21') then Q2_balance else 0 end as stayed_in_stage_2) C
from HEF_2021Q1 A inner join HEF_2021Q2 B
on A.ACCT_ID =B.ACCT_ID


Select sum(from_stage_1_to_stage_2) as sum_from_stage1_to_stage2, sum(from_stage_2_to_stage_1) as sum_from_stage_2_to_stage_1,
    sum(stayed_in_stage_1) as sum_stayed_in_stage_1, sum(stayed_in_stage_2) as sum_stayed_in_stage_2
from (
    select distinct a.acct_id, a.stage as Q1_stage, b.stage as Q2_stage,
    a.balance as Q1_balance, b.balance as Q2_balance,
    case when Q1_Stage <Q2_Stage then Q2_balance else 0 end as from_stage_1_to_stage_2,
    case when Q1_Stage >Q2_Stage then Q2_balance else 0 end as from_stage_2_to_stage_1,
    case when (Q1_Stage =Q2_Stage and Q1_Stage ='1') then Q2_balance else 0 end as stayed_in_stage_1,
    case when (Q1_Stage =Q2_Stage and Q1_Stage ='21') then Q2_balance else 0 end as stayed_in_stage_2
    from HEF_2021Q1 A inner join HEF_2021Q2 B on A.ACCT_ID = B.ACCT_ID
) C

Thank you it works!