CODEWITHSUNDEEP
sql-servert-sql
awilinsk
awilinsk

Reputation: 2044

T-SQL Update From Order By

I am trying to write a Stored Procedure that will give me Audit information between a date range. The Audit table stores the Audit Date, Column Name and Old Value. I want to display the Old Value and the New Value in the result set. I need to get the new value from the next most recent Audit entry or from the entity itself. The stored procedure is a multi step approach to get the result set I need.

  1. Create a @results temp table with the Audit records from within the date range.
  2. Create a @currentValues temp table with the current values from the entity.
  3. Update the @results table to store the new value

Here is the structure of the Audit Table:

Here is the sql:

CREATE PROC GetAuditSummary
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
AS

DECLARE @Results table(
    AuditId uniqueidentifier,
    AuditDate datetime,
    UserId uniqueidentifier,
    EndityId uniqueidentifier,
    ColumnName nvarchar(100),
    OldValue nvarchar(MAX),
    NewValue nvarchar(MAX)
INSERT INTO @Results(AuditId, AuditDate, UserId, EntityId, ColumnName, OldValue)
SELECT AuditId, AuditDate, UserId, EntityId, ColumnName, OldValue
FROM Audit
WHERE (AuditDate >= @StartDate) AND (AuditDate < @EndDate)

DECLARE @CurrentValues table(
    EntityId uniqueidentifier,
    ColumnName nvarchar(100),
    Value nvarchar(MAX)
)
--Lengthy Code to fill @CurrentValues temp table. Assume @CurrentValues is populated

UPDATE @Results
SET NewValue = n.Value
FROM @Results r INNER JOIN
    (SELECT AUditId, AuditDate, EntityId, ColumnName, OldValue AS Value
     FROM Audit
     UNION ALL
     SELECT NULL, GETDATE(), EntityId, ColumnName, Value
     FROM @CurrentValues
     ORDER BY AuditDate DESC
    ) n ON n.EntityId = r.EntityId AND
           n.ColumnNmae = r.ColumName NAD
           n.AuditDate > r.AuditDate

SELECT * FROM @Results ORDER BY AuditDate DESC

Now, and correct me if I'm wrong, when the update statement executes, the NewValue should be set to the last matching row in the joined result set and since I have the subquery ordered by AuditDate, the AuditDate closest to the current record from @Results should be the value that's set to NewValue. I've tried this, but I get an error telling me I can't use an Order By in a subquery. Is there another way to do this? I'm open to any suggestions, but I need to take performance into consideration as there is a chance of having thousands of rows in the result.

--EDIT

Here is one way to get it working, but I'm not sure it's the best on performance.

UPDATE @Results
SET NewValue = COALESCE(
    (SELECT TOP 1 a.OldValue
     FROM Audit a
     WHERE (a.EntityId = r.EntityId) AND
        (a.ColumnName = r.ColumnName) AND
        (a.AuditDate > r.AuditDate)
     ORDER BY a.AuditDate),
    (SELECT TOP 1 c.Value
     FROM @CurrentValues c
     WHERE (c.EntityId = r.EntityId) AND
        (c.ColumnName = r.ColumnName))
FROM @Results r

Upvotes: 1

Views: 1587

Answers (1)

Vasanth
Vasanth

Reputation: 1710

I would use Row_Number or Rank function to get the row after last matching row.

Following example should work, you may want to change (order by n.AuditDate) to (order by n.AuditDate desc) if you want most recent record after matching date.

  UPDATE @Results
  SET NewValue = n.Value
  FROM @Results r 
     INNER JOIN
               (
                select n.EntityId, n.ColumnName, n.Value, Row_Number() over(partition by n.EntityId, n.ColumnName order by n.AuditDate) RowNumber
                from 
                 @Results ir
                 inner join (
                               SELECT AuditDate, EntityId, ColumnName, OldValue AS Value
                               FROM Audit
                               UNION ALL
                               SELECT GETDATE(), EntityId, ColumnName, Value
                               FROM @CurrentValues
                            ) inn on inn.EntityId = ir.EntityId AND
                                        inn.ColumnNmae = ir.ColumName NAD
                                        inn.AuditDate > ir.AuditDate

               ) n ON n.EntityId = r.EntityId AND
                      n.ColumnNmae = r.ColumName AND
                      n.RowNumber = 1

Upvotes: 2

Related Questions