By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[DimSalesLeadAttributes](
[DWKey] [int] NOT NULL,
[OriginalSalesLeadId] [int] NOT NULL,
[LeadStage] [nvarchar](100) NULL,
[OpportunityStage] [nvarchar](100) NULL,
[PreviousStages] [nvarchar](50) NULL,
[CRMLeadOpportunity] char default 'y',
[ObjectChangeId] int
);
INSERT INTO [dbo].[DimSalesLeadAttributes] ([DWKey],[OriginalSalesLeadId],[LeadStage],[OpportunityStage],[PreviousStages])
VALUES(107309,20240220,NULL,'SAL',NULL),
(108000,20240220,NULL,'Hesitating',NULL),
(109442,20240220,'Lost',NULL,NULL),
(111224,20240220,'Lost',NULL,NULL),
(111458,20240220,'Lost',NULL,NULL),
(111730,20240220,'Lost',NULL,NULL),
(111983,20240220,'Lost',NULL,NULL),
(113011,20240220,'Lost',NULL,NULL);
-- >= 2022 version.
with
-- Simple intermediate table to hold the result of the coalesce:
stage as (select *, coalesce(OpportunityStage, LeadStage) stage from DimSalesLeadAttributes),
-- Add the previous stage to each entry:
prev as
(
select
*,
lag(stage) over (partition by OriginalSalesLeadId order by DWKey) prev
from stage
)
SELECT [DWKey]
, [ObjectChangeId]
, [OriginalSalesLeadId]
, [OpportunityStage]
| DWKey | ObjectChangeId | OriginalSalesLeadId | OpportunityStage | LeadStage | LastStage |
|---|---|---|---|---|---|
| 107309 | null | 20240220 | SAL | null | null |
| 108000 | null | 20240220 | Hesitating | null | SAL |
| 109442 | null | 20240220 | null | Lost | Hesitating |
| 111224 | null | 20240220 | null | Lost | Hesitating |
| 111458 | null | 20240220 | null | Lost | Hesitating |
| 111730 | null | 20240220 | null | Lost | Hesitating |
| 111983 | null | 20240220 | null | Lost | Hesitating |
| 113011 | null | 20240220 | null | Lost | Hesitating |
| DWKey | ObjectChangeId | OriginalSalesLeadId | OpportunityStage | LeadStage | LastStage |
|---|---|---|---|---|---|
| 107309 | null | 20240220 | SAL | null | null |
| 108000 | null | 20240220 | Hesitating | null | SAL |
| 109442 | null | 20240220 | null | Lost | Hesitating |
| 111224 | null | 20240220 | null | Lost | Hesitating |
| 111458 | null | 20240220 | null | Lost | Hesitating |
| 111730 | null | 20240220 | null | Lost | Hesitating |
| 111983 | null | 20240220 | null | Lost | Hesitating |
| 113011 | null | 20240220 | null | Lost | Hesitating |
| DWKey | ObjectChangeId | OriginalSalesLeadId | OpportunityStage | LeadStage | LastStage | PreviousStage |
|---|---|---|---|---|---|---|
| 107309 | null | 20240220 | SAL | null | SAL | null |
| 108000 | null | 20240220 | Hesitating | null | SAL | SAL |
| 109442 | null | 20240220 | null | Lost | Hesitating | Hesitating |
| 111224 | null | 20240220 | null | Lost | Lost | Hesitating |
| 111458 | null | 20240220 | null | Lost | Lost | Hesitating |
| 111730 | null | 20240220 | null | Lost | Lost | Hesitating |
| 111983 | null | 20240220 | null | Lost | Lost | Hesitating |
| 113011 | null | 20240220 | null | Lost | Lost | Hesitating |