Difference in value

• faulknerwilliam2

  SSCrazy

  Points: 2299

  More actions

  November 13, 2017 at 1:36 am

  #324421

  I have a simple audit table:

  PersonID Date  Value

  01   1/1/17 Faulkner
  01   1/2/17 Hemingway

  Which shows me where a person's surname has changed and the date this happened.

  Please can someone advise if there is a report I can run / which T-SQL I should use that will just bring up when a person's surname has changed, e.g. just display row 2 above?

  Suggestions /ideas very welcome.

  Thanks

• Thom A

  SSC Guru

  Points: 99130

  More actions

  November 13, 2017 at 3:01 am

  #1967725

  Use a CTE and ROW_NUMBER(), ordered by Date Descending. Then only return the rows (from the CTE) where the value of your ROW_NUMBER() column is 1.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 13, 2017 at 3:14 am

  #1967726

  Thom A - Monday, November 13, 2017 3:01 AM

  Use a CTE and ROW_NUMBER(), ordered by Date Descending. Then only return the rows (from the CTE) where the value of your ROW_NUMBER() column is 1.

  I think that'll just give the most recent name for each person, regardless of whether his name has actually changed.  The requirement is to show all names for persons whose names have changed.  This should do it, with the caveat that it won't filter out the case where someone's name has changed, for example, from Faulkner to Faulkner.

  WITH Counts AS (
     SELECT
        PersonID
    ,    Date
    ,    Value
    ,    COUNT(*) OVER (PARTITION BY PersonID) AS NoofNames
    ,    ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Date DESC) AS NameNo
    FROM Mytable
    )
SELECT
     PersonID
,    Date
,    Value
FROM Counts
WHERE NoofNames > 1
AND NameNo = 1;

  John

• Thom A

  SSC Guru

  Points: 99130

  More actions

  November 13, 2017 at 3:36 am

  #1967734

  John Mitchell-245523 - Monday, November 13, 2017 3:14 AM

  Thom A - Monday, November 13, 2017 3:01 AM

  Use a CTE and ROW_NUMBER(), ordered by Date Descending. Then only return the rows (from the CTE) where the value of your ROW_NUMBER() column is 1.

  I think that'll just give the most recent name for each person, regardless of whether his name has actually changed.  The requirement is to show all names for persons whose names have changed.  This should do it, with the caveat that it won't filter out the case where someone's name has changed, for example, from Faulkner to Faulkner.

  I'm not sure that is what the OP is after:

  The OP
  Please can someone advise if there is a report I can run / which T-SQL I should use that will just bring up when a person's surname has changed, e.g. just display row 2 above?

  Emphasis mine. 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk