Query to return two columns from single field with mutually exclusive constraint

I wanted to get two columns displayed basically from single field; one with current plan value and other with the immediate previous plan with the below table structure. I want to select only records with StatusId != 2 (records count=3). And to display the PrevPlan column which has a StatusId = 2.

And wanted an output like below.

Queries tried

I have used usual inner joins of these tables with an outer apply (sorting using CreatedDate Desc as there are more older values) to get the PrevPlan. But it is updating the entire PrevPlan column with same value. The count of returned records is 3 which is correct.

    OUTER APPLY
        (
            SELECT TOP 1
                 PlanPrev.Id
                ,PlanPrev.[Name]                 
            FROM
                Plans PlanPrev
                inner join Trials tPrev on tPrev.PlanId = PlanPrev.Id
            WHERE
                tPrev.StatusId = 2
            ORDER BY
                tPrev.CreatedDate Desc
        ) AS PlanPrev

So I used a CTE below, which is updating but it is returning all the 6 records instead of 3.

WITH ContractsCTE (ContractId, CurrentPlan, PrevPlan)
AS
(
Select ContractId,  p.[Name] as CurrentPlan, p.[Name] as PrevPlan
from Trials t
inner join Contracts c on c.Id = t.ContractId
inner join Plans p on t.PlanId = p.Id
where  t.StatusId != 2
)
select ContractId,  cte.CurrentPlan, p.[Name] as PrevPlan
from ContractsCTE cte
left join Trials t1 on cte.ContractId = t1.ContractId
left join Contracts c1 on c1.Id = t1.ContractId
left join Plans p1 on t1.PlanId = p1.Id 
and t1.StatusId = 2 
Order by CreateDate desc;

Is there a way we can achieve it without using any Temp tables/update queries? Any help would highly appreciated.

Answer

We can separate the previous plan details first and then use that.

I slightly modified the second part of the CTE as below:

select cte.ContractId,  cte.CurrentPlan, p1.[Name] as PrevPlan
from ContractsCTE cte
left join (SELECT t.CONTRACTID, t.STATUSID, t.PLANID, row_number() OVER (PARTITION BY t.CONTRACTID, t.STATUSID ORDER BY t.CREATEDATE DESC) rn FROM @TRAILS t WHERE t.STATUSID = 2)t1 
    on cte.ContractId = t1.ContractId AND t1.rn = 1
left join @CONTRACTS c1 on c1.Id = t1.ContractId AND t1.rn = 1
left join @PLANS p1 on t1.PlanId = p1.Id AND t1.rn = 1
Order by cte.CONTRACTID

db<>fiddle here.

Advertisement

Answer