By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) Mar 4 2020 00:59:26 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
DECLARE @TRAILS TABLE (ID INT, CONTRACTID INT, STATUSID INT, PLANID INT, CREATEDATE VARCHAR(10))
INSERT INTO @TRAILS SELECT 1, 1, 1, 1, '7/16'
INSERT INTO @TRAILS SELECT 2, 1, 2, 2, '7/15'
INSERT INTO @TRAILS SELECT 3, 2, 1, 3, '7/16'
INSERT INTO @TRAILS SELECT 4, 3, 1, 1, '7/18'
INSERT INTO @TRAILS SELECT 5, 3, 2, 2, '7/19'
INSERT INTO @TRAILS SELECT 6, 3, 2, 3, '7/17'
SELECT * FROM @TRAILS
DECLARE @CONTRACTS TABLE (ID INT, [NAME] VARCHAR(100), CREATEDATE VARCHAR(10), UPDATEDATE VARCHAR(10))
INSERT INTO @CONTRACTS SELECT 1, 'Con1', '7/15', '7/16'
INSERT INTO @CONTRACTS SELECT 2, 'Con2', '7/16', NULL
INSERT INTO @CONTRACTS SELECT 3, 'Con3', '7/17', '7/19'
SELECT * FROM @CONTRACTS
DECLARE @PLANS TABLE (ID INT, [NAME] VARCHAR(100))
INSERT INTO @PLANS SELECT 1, 'Plan1'
INSERT INTO @PLANS SELECT 2, 'Plan2'
INSERT INTO @PLANS SELECT 3, 'Plan3'
SELECT * FROM @PLANS
--numbering latest plan (previous)
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
;WITH ContractsCTE (ContractId, CurrentPlan, PrevPlan) AS
(
Select ContractId, p.[Name] as CurrentPlan, p.[Name] as PrevPlan
from @TRAILS t
inner join @CONTRACTS c on c.Id = t.ContractId
inner join @PLANS p on t.PlanId = p.Id
where t.StatusId != 2
)
select cte.ContractId, cte.CurrentPlan, p1.[Name] as PrevPlan
| ID | CONTRACTID | STATUSID | PLANID | CREATEDATE |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 7/16 |
| 2 | 1 | 2 | 2 | 7/15 |
| 3 | 2 | 1 | 3 | 7/16 |
| 4 | 3 | 1 | 1 | 7/18 |
| 5 | 3 | 2 | 2 | 7/19 |
| 6 | 3 | 2 | 3 | 7/17 |
| ID | NAME | CREATEDATE | UPDATEDATE |
|---|---|---|---|
| 1 | Con1 | 7/15 | 7/16 |
| 2 | Con2 | 7/16 | null |
| 3 | Con3 | 7/17 | 7/19 |
| ID | NAME |
|---|---|
| 1 | Plan1 |
| 2 | Plan2 |
| 3 | Plan3 |
| CONTRACTID | STATUSID | PLANID | rn |
|---|---|---|---|
| 1 | 2 | 2 | 1 |
| 3 | 2 | 2 | 1 |
| 3 | 2 | 3 | 2 |
| ContractId | CurrentPlan | PrevPlan |
|---|---|---|
| 1 | Plan1 | Plan2 |
| 2 | Plan3 | null |
| 3 | Plan1 | Plan2 |