December 14, 2017 at 1:12 pm
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.
December 14, 2017 at 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;
December 14, 2017 at 2:24 pm
Chris Harshman - Thursday, December 14, 2017 1:50 PMSince 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!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply