How to do calculations and then compare them in SQL Server

Question

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

If TW01SS is bigger, what complement row should it update? In 2208011011+TW01SS 50 > 1+1 so 2208011011+TW01SS row, complement column should = 50? and if its the other way around, if D1+D2 > SS then update both D1 and D2 with a value? — griv, Sep 01 '22 at 17:46
If TW01SS > TW01D1+TW01D2 then in Complement should value of TW01SS and if TW01SS < TW01D1+TW01D2 then SUM of TW01D and TW01D2. — Marat Safyanov, Sep 01 '22 at 18:38
Great. Thanks, I think I understand what you want. I've posted an answer. In the future, please read [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) and update the question with table descriptions, data examples (in a table, not an image), and what you attempted so far. You can even include a Fiddle like I did in my answer below, it'll help you get an answer quickly. — griv, Sep 01 '22 at 19:46
Please do not post images of code or data, please paste them in as text. Expected results would also help — Charlieface, Sep 01 '22 at 21:18

griv · Answer 1 · 2022-09-01T19:37:53.983

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 '%D1' OR Test LIKE '%D2'
    
),
SS_Count
AS
-- Define SS Count CTE Query.
(
    SELECT RecordNumber AS SS_ID,
    SUM(Value) OVER(PARTITION BY RecordNumber) AS SS_Total
    FROM TestA 
    WHERE Test LIKE '%SS'
)
UPDATE A 
SET Complement = 
     CASE WHEN D1D2_Total > SS_Total
      THEN D1D2_Total
      ELSE SS_Total
     END 
FROM TestA AS A
LEFT JOIN D1D2_Count ON RecordNumber = D1D2_ID
LEFT JOIN SS_Count ON RecordNumber = SS_ID

See Fiddle.

Optionally, you can add a WHERE clause to the end of the UPDATE statement so that it'll only update the Complement that has the greater total

If D1+D2 > SS, UPDATE Only the D1+D2 rows
Otherwise, UPDATE Only the SS rows.

Like this:

WHERE Test LIKE 
     CASE WHEN D1D2_Total > SS_Total
      THEN '%D1'
      ELSE '%SS'
     END 
     OR Test LIKE 
     CASE WHEN D1D2_Total > SS_Total
      THEN '%D2'
      ELSE '%SS'
     END

shawnt00 · Answer 2 · 2022-09-01T20:08:56.210

select *, case when SS >= DX then SS else DX end
from T cross apply (values (
    sum(case when Test = 'TW01SS1' then Value end)
        over (partition by RecordNumber),
    sum(case when Test like 'TW01D[12]' then Value end)
        over (partition by RecordNumber))
) s(SS, DX);

You can use else coalesce(DX, SS) if null/missing tests are at play.

As an update:

update T
set Complement = (
    select case when SS >= DX then SS else DX end
    from T t2 cross apply (values (
        sum(case when Test = 'TW01SS1' then Value end),
        sum(case when Test like 'TW01D[12]' then Value end)
    ) s(SS, DX)
    where t2.RecordNumber = T.RecordNumber
);

How to do calculations and then compare them in SQL Server

2 Answers2