Change sign

Transact-SQL
1

Hi, I would like to create a new column "New Amount". The values in the new column will all come from the column "Amount", which are all positive. They new values will either remain positive or will change into negative, based on comparison of two other columns ID and ZID. The idea is that after conversion of the values in the Amount column, the total New Amount for ID (100,1) should be zero and the total New Amount for ID (200,1) should be zero as well.
Here is an example:

ID ZID Amount New Amount
(100,1) (100,1,1) 10 10 should remain positive
(100,1) (100,1,2) 10 should convert this amount to negative 10 because value 2 from ZID (100,1,2) is the MAXIMUM value for ID (100,1)
(200,1) (200,1,1) 2 2 should remain positive
(200,1) (200,1,2) 3 3 should remain positive
(200,1) (200,1,3) 5 should convert this amount to negative 5 because value 3 from ZID (200,1,3) is the MAXIMUM value for ID (200,1)
2

The data in the ID columns looks strange. Anyway here is consumable test data which you should have provided.

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t
(
	ID varchar(20) NOT NULL
	,ZID varchar(20) NOT NULL
	,Amount int NOT NULL
	,PRIMARY KEY (ID, ZID)
);
INSERT INTO #t
VALUES ('(100,1)', '(100,1,1)', 10)
	,('(100,1)', '(100,1,2)', 10)
	,('(200,1)', '(200,1,1)', 2)
	,('(200,1)', '(200,1,2)', 3)
	,('(200,1)', '(200,1,3)', 5);
GO

The following will work in all supported versions of SQL Server:

SELECT ID, ZID
	,CASE
		WHEN ZID = MAX(ZID) OVER (PARTITION BY ID)
		THEN - Amount
		ELSE Amount
	END AS Amount
FROM #t;
1 Like
3

Many thanks @Ifor. This is exactly what I was looking for.