Tough SQL Rank query

Question

Scenario: primary user table plus a separate audit table that tracks changes to the user's name. Each time a user is added or part of their name is edited, we write a row to the audit table.

Trying to write a query that pulls the most immediate former name

select nsh.AuthEmail, nsh.UserID, nsh.name_lastnamefirst, t.FormerName, t.RankOrder
from (
Select 
    an.AuditNameID, nsh.AuthEmail, nsh.UserID, nsh.name_lastnamefirst, 
    FormerName = CASE WHEN RTRIM(an.LastName) <> RTRIM(nsh.LastName) OR RTRIM(an.FirstName) <> RTRIM(nsh.FirstName) OR RTRIM(an.Suffix) <> RTRIM(nsh.Suffix) OR RTRIM(an.MaidenName)<>RTRIM(nsh.MaidenName) THEN LTRIM(an.LastName + ' ' + an.Suffix + ', ' + an.FirstName + ' ' + ISNULL(an.MiddleName,''))
    ELSE null       
    END,
    RANK() over (partition by an.UserID order by an.AuditNameID DESC) RankOrder
From [dbo].[AuditName] an
INNER JOIN dbo.StudentPrograms p ON an.UserID = p.UserID 
INNER JOIN dbo.NameScalarHelper nsh ON p.UserID = nsh.UserID 
WHERE p.SiteProgramID = 139 AND p.IsActive =1 
) t
RIGHT OUTER JOIN dbo.NameScalarHelper nsh ON nsh.UserID = t.UserID
where FormerName is not null

The problem is that I can't figure out how to return the data from audit table where the RANK is RANK -1 because the top rank is the current data. Let me know if any ideas.

Answer 1

Looking at your requirement you basicly need to return latest name that doesn't match current one from your Audit table.

I think you could use OUTER APPLY to achieve that:

SELECT *
FROM [dbo].[StudentPrograms] AS SP
INNER JOIN [dbo].[NameScalarHelper] AS NSH
  ON NSH.UserID = SP.UserID
OUTER APPLY (
  SELECT TOP (1) *
  FROM [dbo].[AuditName] AS AN
  WHERE AN.UserID = SP.UserID
    AND (
      RTRIM(AN.LastName) <> RTRIM(NSH.LastName)
      OR RTRIM(AN.FirstName) <> RTRIM(NSH.FirstName)
      OR RTRIM(AN.Suffix) <> RTRIM(NSH.Suffix)
      OR RTRIM(AN.MaidenName) <> RTRIM(NSH.MaidenName)
      )
  ORDER BY AuditNameID DESC
  ) AS AN
WHERE SP.SiteProgramID = 139
  AND SP.IsActive = 1;

This will find latest name from your audit table which doesn't match latest one.

By the way, I'd strongly suggest to clean up your database and remove any trailing/leading spaces, so that you don't need to use LTRIM() or RTRIM() in your where clause so that SQL Server would be able to make use of indexes. Read this article for more details.

SELECT *
FROM [dbo].[StudentPrograms] AS SP
INNER JOIN [dbo].[NameScalarHelper] AS NSH
  ON NSH.UserID = SP.UserID
OUTER APPLY (
  SELECT TOP (1) *
  FROM [dbo].[AuditName] AS AN
  WHERE AN.UserID = SP.UserID
    AND (
      AN.LastName <> NSH.LastName
      OR AN.FirstName <> NSH.FirstName
      OR AN.Suffix <> NSH.Suffix
      OR AN.MaidenNam) <> NSH.MaidenName
      )
  ORDER BY AuditNameID DESC
  ) AS AN
WHERE SP.SiteProgramID = 139
  AND SP.IsActive = 1;

I was trying to understand the way you store data and replicated a tiny example:

DECLARE @User TABLE
(
  UserID INT
  , FirstName VARCHAR(50)
  , LastName VARCHAR(50)
);

DECLARE @Audit TABLE
(
  AuditID INT IDENTITY(1, 1)
  , UserID INT
  , FirstName VARCHAR(50)
  , LastName VARCHAR(50)
);

INSERT INTO @User (UserID, FirstName, LastName)
VALUES (1, 'Ben', 'White');

INSERT INTO @Audit (UserID, FirstName, LastName)
VALUES (1, 'Ben', 'White');

SELECT *
FROM @User AS U
OUTER APPLY (
  SELECT TOP (1) *
  FROM @Audit AS A
  WHERE A.UserID = U.UserID
    AND (
      A.FirstName <> U.FirstName
      OR A.LastName <> U.LastName
    )
  ORDER BY A.AuditID DESC
  ) AS A;

UPDATE U
SET U.LastName = 'Whiter'
FROM @User AS U
WHERE U.UserID = 1;

INSERT INTO @Audit (UserID, FirstName, LastName)
VALUES (1, 'Ben', 'Whiter');

SELECT *
FROM @User AS U
OUTER APPLY (
  SELECT TOP (1) *
  FROM @Audit AS A
  WHERE A.UserID = U.UserID
    AND (
      A.FirstName <> U.FirstName
      OR A.LastName <> U.LastName
    )
  ORDER BY A.AuditID DESC
  ) AS A;

UPDATE U
SET U.LastName = 'Whitest'
FROM @User AS U
WHERE U.UserID = 1;

INSERT INTO @Audit (UserID, FirstName, LastName)
VALUES (1, 'Ben', 'Whitest');

SELECT *
FROM @User AS U
OUTER APPLY (
  SELECT TOP (1) *
  FROM @Audit AS A
  WHERE A.UserID = U.UserID
    AND (
      A.FirstName <> U.FirstName
      OR A.LastName <> U.LastName
    )
  ORDER BY A.AuditID DESC
  ) AS A;

INSERT INTO @User (UserID, FirstName, LastName)
VALUES (2, 'Tom', 'Brooks');

INSERT INTO @Audit (UserID, FirstName, LastName)
VALUES (2, 'Tom', 'Brooks');

SELECT *
FROM @User AS U
OUTER APPLY (
  SELECT TOP (1) *
  FROM @Audit AS A
  WHERE A.UserID = U.UserID
    AND (
      A.FirstName <> U.FirstName
      OR A.LastName <> U.LastName
    )
  ORDER BY A.AuditID DESC
  ) AS A;

I assumed that when you create a user - you also add record to Audit table for consistency. Each time you make an update - you also log that into Audit table. Finally I just added yet another user and ran the query.

That's the output for each query:

User was created:

UserID FirstName LastName AuditID UserID FirstName LastName 
------ --------- -------- ------- ------ --------- -------- 
1      Ben       White    null    null   null      null  

Its' last name was changed first time:

UserID FirstName LastName AuditID UserID FirstName LastName 
------ --------- -------- ------- ------ --------- -------- 
1      Ben       Whiter   1       1      Ben       White    

Its' last name was changed second time:

UserID FirstName LastName AuditID UserID FirstName LastName 
------ --------- -------- ------- ------ --------- -------- 
1      Ben       Whitest  2       1      Ben       Whiter   

A new user has been added:

UserID FirstName LastName AuditID UserID FirstName LastName 
------ --------- -------- ------- ------ --------- -------- 
1      Ben       Whitest  2       1      Ben       Whiter   
2      Tom       Brooks   null    null   null      null    

Everything else is just formatting and you should not do that in SQL Server - this should be done in application layer.

Answer 2

By looking at your query, I'm guessing you are getting back all of a person's past names, since you have no filter based on the RankOrder that you've created. Your current name should be 1 in the RankOrder, I assume, and so your most recent previous name would be ranked 2. You can add this to your derived table's where clause like this:

    select nsh.AuthEmail, nsh.UserID, nsh.name_lastnamefirst, t.FormerName, t.RankOrder
from (
Select 
    an.AuditNameID, nsh.AuthEmail, nsh.UserID, nsh.name_lastnamefirst, 
    FormerName = CASE WHEN RTRIM(an.LastName) <> RTRIM(nsh.LastName) OR RTRIM(an.FirstName) <> RTRIM(nsh.FirstName) OR RTRIM(an.Suffix) <> RTRIM(nsh.Suffix) OR RTRIM(an.MaidenName)<>RTRIM(nsh.MaidenName) THEN LTRIM(an.LastName + ' ' + an.Suffix + ', ' + an.FirstName + ' ' + ISNULL(an.MiddleName,''))
    ELSE null       
    END,
    RANK() over (partition by an.UserID order by an.AuditNameID DESC) RankOrder
From [dbo].[AuditName] an
INNER JOIN dbo.StudentPrograms p ON an.UserID = p.UserID 
INNER JOIN dbo.NameScalarHelper nsh ON p.UserID = nsh.UserID 
WHERE p.SiteProgramID = 139 AND p.IsActive =1 and RankOrder = 2
) t
RIGHT OUTER JOIN dbo.NameScalarHelper nsh ON nsh.UserID = t.UserID
where FormerName is not null

Let me know if I am missing something.