Alternative to below query to replace while loop to get same output.

• myzus2004

  Old Hand

  Points: 378

  More actions

  September 18, 2018 at 9:16 am

  #379187

  Hello Guys,

  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. Whats the best possible way to get this output. 
  

  Below is the code which i am not happy with - any alternative solution where we can replace while loop using some other logic.

  Any help is 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


• pietlinden

  SSC Guru

  Points: 63428

  More actions

  September 19, 2018 at 1:42 pm

  #2006335

  If all you want is the previous value, then why not just use LAG()?  For the first value in the PARTITION BY clause, it will be NULL. For all others, it will point to the previous value in the "sequence" (determined by the ORDER BY clause in the window function).

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  September 19, 2018 at 2:50 pm

  #2006356

  pietlinden - Wednesday, September 19, 2018 1:42 PM

  If all you want is the previous value, then why not just use LAG()?  For the first value in the PARTITION BY clause, it will be NULL. For all others, it will point to the previous value in the "sequence" (determined by the ORDER BY clause in the window function).

  The LAG function allows you to specify a value to use for the first record, so the value will not necessarily be NULL, although I see no reason to specify a value in this particular case.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA