Help with comparing previous row data values

• seubanks00

  Mr or Mrs. 500

  Points: 591

  More actions

  December 14, 2017 at 1:12 pm

  #325136

  I am having trouble trying to build the query that would populate the 'swap' column. It should have a value of 1 in a case where the INC & DEC values 'swapped' from one record to the next. What is a good way to go about this?
  Code to create sample data: 

  IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

  CREATE TABLE #mytable
   (
   ID            INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   TestValue    INT,
   [Increase]    INT,
   [Decrease]    INT,
   [SWAP]        INT
   )
  
INSERT INTO #mytable
    (TestValue,Increase,[Decrease],[SWAP])
SELECT '75','0','0','0'UNION ALL
SELECT '77','1','0','0'UNION ALL
SELECT '75','0','1','1'UNION ALL
SELECT '76','1','0','1'UNION ALL
SELECT '78','1','0','0'UNION ALL
SELECT '67','0','1','1'
 
SELECT *
FROM #mytable

  Thanks in advance for taking the time to look at it.

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  December 14, 2017 at 1:50 pm

  #1972222

  Since you're in 2008, you can't use the more modern Windowing function methods of doing this, so you would need a self join.  So maybe something like this?
  UPDATE cur SET
  SWAP = CASE WHEN cur.Increase <> prev.Increase AND cur.Decrease <> prev.Decrease THEN 1 ELSE 0 END
FROM #mytable cur
  LEFT OUTER JOIN #mytable prev ON cur.ID = prev.ID + 1;

• seubanks00

  Mr or Mrs. 500

  Points: 591

  More actions

  December 14, 2017 at 2:24 pm

  #1972223

  Chris Harshman - Thursday, December 14, 2017 1:50 PM

  Since you're in 2008, you can't use the more modern Windowing function methods of doing this, so you would need a self join.  So maybe something like this?
  UPDATE cur SET
  SWAP = CASE WHEN cur.Increase <> prev.Increase AND cur.Decrease <> prev.Decrease THEN 1 ELSE 0 END
FROM #mytable cur
  LEFT OUTER JOIN #mytable prev ON cur.ID = prev.ID + 1;

  That seems to work - Thanks!