T-SQL ROW_NUMBER() Up a creek without a paddle

Question

I am trying to select the previous status of a claim from transaction data. I have used the code below

DECLARE @InputDate DATE = '2018-10-01'  
DECLARE @DateOne   DATE = DATEADD(M, -1, DATEADD(mm, DATEDIFF(m, 0, @InputDate), 0))    
DECLARE @DateTwo   DATE = DATEADD(D, -1, DATEADD(mm, DATEDIFF(m, 0, @InputDate), 0))

SELECT
    [Claim Number], [Partner], [Scheme],
    [Claim Status], [Trans ID],
    ROW_NUMBER() OVER (PARTITION BY [Claim Number], [Claim Status] ORDER BY [Claim Number], [Trans ID] DESC) AS Row#
FROM
    (SELECT
         dc.ClaimNumber        as [Claim Number],
         dbp.BusinessPartner   as [Partner],
         ds.Scheme             as [Scheme],
         CONVERT(VARCHAR(10), dc.NotificationDate, 103) as [Notification Date],
         CASE 
            WHEN LEN(dcs.[ClaimStatus]) < 1 
               THEN NULL
               ELSE 
                  CASE WHEN dcs.[ClaimStatus] = 'Inactive' 
                            AND dtuat.UserFullName = 'Rodger Recovery' 
                          THEN 'Recovery' 
                          ELSE dcs.[ClaimStatus] 
                  END
         END as [Claim Status],
         CONVERT(VARCHAR(10), Library.ufConvertIntToDate(ftcs.TransactionDimDateId), 121)     as [Transaction Date],
         ftcs.TransactionDimDateId    as [Trans ID] 
     FROM   
         TravelClaim.uvDimClaimA1R2V1 dc 
     JOIN
         TravelClaim.uvFactTravelClaimStatusA1R2V2 ftcs ON dc.DimClaimId =ftcs.DimClaimId
     JOIN 
         TravelClaim.uvDimClaimStatusA1R2V2 dcs ON ftcs.DimClaimStatusId = dcs.DimClaimStatusId
     JOIN 
         TravelClaim.uvDimBusinessPartnerA1R2V1 dbp ON ftcs.DimBusinessPartnerId = dbp.DimBusinessPartnerId
     JOIN 
         TravelClaim.uvDimSchemeA1R2V1 ds ON ftcs.DimSchemeId = ds.DimSchemeId
     JOIN 
         TravelClaim.uvDimTeamUserA2R2V1 dtuat ON ftcs.AccessedByDimTeamUserId = dtuat.DimTeamUserId
     WHERE
         dcs.ClaimStatus NOT LIKE 'Query'
         AND dc.ClaimNumber NOT LIKE 'Unknown') AS a
 WHERE
     [Transaction Date] >= @DateOne 
     AND [Transaction Date] <= @DateTwo 
     AND [claim number] = 'abc/1234567' 
 ORDER BY 
     [claim number], [Trans ID]desc

This returns the following result:

Claim Number Partner    Scheme          Claim Status Trans ID   Row#
---------------------------------------------------------------------
abc/1234567 something   something else  Settled      20180922   1
abc/1234567 something   something else  Settled      20180921   2
abc/1234567 something   something else  Settled      20180917   3
abc/1234567 something   something else  Open         20180914   1
abc/1234567 something   something else  Settled      20180912   4
abc/1234567 something   something else  Settled      20180905   5
abc/1234567 something   something else  Settled      20180904   6

I need to select each time the status has changed which would be

Claim Number Partner    Scheme          Claim Status    Trans ID    Row#
------------------------------------------------------------------------
abc/1234567 something   something else  Settled         20180922    1
abc/1234567 something   something else  Open            20180914    1
abc/1234567 something   something else  Settled         20180912    4

So the idea was to select the rows where row# = 1 however this would not give me the correct result as I would be missing the settled to open change row# 4

If this had worked I intended to use row_number again and select all the rows with row# = 2 then join this back to my main data giving me a previous status column and if necessary =3 etc in additional columns.

Any help would be appreciated.

Thanks.

Just use lag and test for null or <> current – paparazzo Oct 04 '18 at 17:15 — paparazzo, Oct 04 '18 at 17:15

Jamie Lester · Answer 1 · 2018-10-04T17:44:50.363

EDIT: Using the LAG or LEAD functions as suggested in the comments would probably better if your version of SQL supports it. I learned something new today!

Here's how I would do it. Of course, you can change the name of cte to whatever you would like, and you will need to fill in the table joins and column selects, but this is the bones of the concept.

;with cte as (
    select *, row_number() over (partition by [Claim Number] order by [Claim Number],[Trans ID] as rowNum
    from ... put your tables here
)
select a.*
from cte a
left join cte b
    on a.rowNum = b.rowNum + 1
where a.[Claim Status] != b.[Claim Status]

T-SQL ROW_NUMBER() Up a creek without a paddle

1 Answers1