CODEWITHSUNDEEP
sqlsql-servert-sql
Myzus
Myzus

Reputation: 23

Alternative to replace while loop using set based logic

I have 2 tables - one having the latest records (#Director table) and other table ( #DirectorAudit ) which has records where changes has taken place. The AuditId is the sequence of changes done

Need to have its output as follows -

https://i.sstatic.net/jN8aN.png

Whats the best possible way to get this output.

Please find below the current code to get output but i am not happy with it. Any alternative solution where we can replace while loop (no cursors plz) or some other logic.

Any help is highly appreciated. Thank you very much.

SET NOCOUNT ON;

        IF OBJECT_id('tempdb..#a') IS NOT NULL                DROP TABLE #a
        IF OBJECT_id('tempdb..#b') IS NOT NULL                DROP TABLE #b
        IF OBJECT_id('tempdb..#c') IS NOT NULL                DROP TABLE #c
        IF OBJECT_id('tempdb..#d') IS NOT NULL                DROP TABLE #d

        IF OBJECT_id('tempdb..#DirectorAudit') IS NOT NULL DROP TABLE #DirectorAudit
        IF OBJECT_id('tempdb..#Director') IS NOT NULL DROP TABLE #Director

        Create table #DirectorAudit(AuditId TINYINT IDENTITY(1,1),DirectorID INT,[Name] varchar(200),Code varchar(5))
        INSERT INTO  #DirectorAudit(DirectorID,[Name],Code)
        SELECT  541,'Steven','A'
        UNION
        SELECT  541,'Roger','A'
        UNION
        SELECT  541,'Mathew','A'
        UNION
        SELECT  541,'Mathew','I'

        Create table #Director(DirectorID INT,[Name] varchar(200),Code varchar(5))
        INSERT INTO  #Director(DirectorID,[Name],Code)
        SELECT 541,'David','A'


        CREATE TABLE #d (DirectorID INT ,FieldName VARCHAR(100), OldValue VARCHAR(4000), NewValue VARCHAR(4000), AuditID int)
        CREATE TABLE #b (DirectorID INT,  Oldvalue VARCHAR(4000),oldCode VARCHAR(200),AuditId INT)
        CREATE TABLE #c (DirectorID INT,  NewValue VARCHAR(4000),oldCode VARCHAR(200),AuditId INT)


        DECLARE  @oldValue VARCHAR(200),
                 @Code VARCHAR(200),
                 @newValue VARCHAR(200),
                 @newCode     VARCHAR(200),    
                 @DirectorID INT = 541

        SELECT  Row_Number() over(order by AuditId asc) as ID,
                a.DirectorID,
                a.Name as NewName, 
                b.Name as OldName,
                a.Code as NewCode, 
                b.Code as oldCode,
                b.AuditID

        INTO     #a
        FROM     #Director a 
                 INNER JOIN #DirectorAudit  b ON a.DirectorID = b.DirectorID 

        WHERE     b.DirectorID = @DirectorID
        order by b.AuditId


         DECLARE @count INT =1 
         DECLARE @init INT = 1 
         DECLARE @tmp_AuditId INT 
         DECLARE @tmp_NextAuditID INT 

         SELECT @count = COUNT(*) FROM #a 

         WHILE @count >= @init
         BEGIN
            SELECT  @tmp_AuditId = AuditId from #a WHERE Id = @init
            select  @tmp_NextAuditID = AuditId from #a WHERE Id = @init+1

            IF @count =1 SET @tmp_NextAuditID = @tmp_AuditId

            SELECT @OldValue = OldName,
                 @Code = oldCode

            from #a 
            where AuditID = @tmp_AuditId

            select  @newValue = OldName,
                    @newCode = oldCode

            from #a 
            where AuditID = @tmp_NextAuditID

            INSERT INTO #b(DirectorID,  OldValue,oldCode, AuditID)
            select            DirectorID, @OldValue,@Code,  AuditID
            FROM    #a
            WHERE AuditId = @tmp_AuditId 

            IF @count <> @init 
            BEGIN
                INSERT INTO #c(DirectorID, NewValue,oldCode, AuditID)
                select            DirectorID, @newValue,@newCode, @tmp_AuditId
                FROM    #a
                WHERE AuditId = @tmp_NextAuditID 
            END
            ELSE
            BEGIN
                INSERT INTO #c(DirectorID, NewValue, oldCode,AuditID)
                select            E.DirectorID, e.Name,e.Code, a.AuditID
                FROM    #Director e 
                        INNER JOIN #a  a            ON    e.DirectorID = a.DirectorID
                WHERE AuditId = @tmp_NextAuditID 
            END    

            SET @init = @init + 1 
         END 

        /*
         select * from #a
         SELECT * FROM #b
         SELECT * FROM #c
        */

        INSERT INTO #d(DirectorID,FieldName, OldValue, NewValue, AuditID)
        SELECT DirectorID,FieldName, OldValue, NewValue, AuditID
        FROM (
                SELECT c.DirectorID,'Name' as FieldName, c.OldValue as OldValue, d.NewValue as NewValue, c.AuditID
                FROM    #b as c
                        inner join #c  as d ON  c.AuditId = d.AuditId
                UNION 
                SELECT c.DirectorID,'Code' as FieldName, c.oldCode as OldValue, d.oldCode as NewValue, c.AuditID
                FROM    #b as c
                        inner join #c  as d ON  c.AuditId = d.AuditId 


            )x 
        order by x.AuditID 

        select a.DirectorID,FieldName, OldValue, NewValue
        from #d a 
        where OLdValue <> NewValue 
        order by a.FieldName desc

Upvotes: 1

Views: 208

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

Here is one approach where you only have to mention the key fields. In this case AutidID and DirectorID

Cross Apply B will convert the record into XML

Cross Apply C will dynamically unpivot your data (excluding specified columns)

Example

;with cte as (
Select A.AuditID
      ,A.DirectorID
      ,C.*
      ,NewValue      = lag(OldValue,1,OldValue) over (Partition By DirectorID,Field Order by case when AuditID is null then 0 else 1 end,AuditID desc)
 From (
        Select * from #DirectorAudit
        Union All
        Select null,* from #Director
      ) A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Field = a.value('local-name(.)','varchar(100)')
                      ,OldValue = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('AuditId','DirectorID','OtherColumnsToExclude')
             ) C
)
Select * 
 From  cte 
 Where OldValue<>NewValue
 Order by Field,AuditID

Returns

AuditID DirectorID  Field   OldValue    NewValue
1       541         Code    A           I
2       541         Code    I           A
2       541         Name    Mathew      Roger
3       541         Name    Roger       Steven
4       541         Name    Steven      David

EDIT - REQUESTED Alternate

;with cte as (
Select A.AuditID
      ,A.DirectorID
      ,B.*
      ,NewValue      = lag(OldValue,1,OldValue) over (Partition By DirectorID,Field Order by case when AuditID is null then 0 else 1 end,AuditID desc)
 From (
        Select * from #DirectorAudit
        Union All
        Select null,* from #Director
      ) A
 Cross Apply ( values ('Name',Name)
                     ,('Code',Code)
             ) B(Field,OldValue)
)
Select * 
 From  cte 
 Where OldValue<>NewValue
 Order by Field,AuditID

Upvotes: 3

Related Questions