How to do calculations and then compare them in SQL Server-CodePudding

I have a table with columns Recordnumber, Test, Value, Date and Complement. Recordnumber and test are the primary key.

I need compare values from TW01SS and TW01D1 TW01D2 with the same Recordnumber and depending on which value is bigger add it to Complement column. Any ideas?

Thank you

CodePudding user response：

You can create two CTE's, one to get the SS count and the other the D1 D2 count then UPDATE the Complement column by the Recordnumber.

See WITH common_table_expression (Transact-SQL)

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

;WITH 
D1D2_Count
AS
-- Define D1 D2 Count CTE Query.
(
    SELECT RecordNumber AS D1D2_ID,
    SUM(Value) OVER(PARTITION BY RecordNumber) AS D1D2_Total
    FROM TestA 
    WHERE Test LIKE '