Aug 03, 2018 04:22 PM|mgebhard|LINK
However, your query doesn't match up with the sample you showed.
I don't know what's wrong.
I am expecting to see mathematical operations of substracting field2 from original amount on field1 and displaying both the original amount (field1), paid amount (field2) and difference.
I work in financial systems and do not recommend the approach. Reconciling and updating the financial records will be near impossible.
I recommend using industry standard financial data entry practices. Then you'll be able to "zero out" mistakes and track what happened over time.
As you requested there are several way to code this. I feel this is the most straightforward to understand. the calculation is placed in a temp table. The temp table is used to update the main table by Id.
IF OBJECT_ID('tempdb..#Accounting') IS NOT NULL
DROP TABLE #Accounting
IF OBJECT_ID('tempdb..#AccountingTemp') IS NOT NULL
DROP TABLE #AccountingTemp
CREATE TABLE #Accounting
Id INT IDENTITY(1,1),
Field1 DECIMAL NOT NULL,
Field2 DECIMAL NOT NULL,
Field3 DECIMAL NULL,
Field4 DECIMAL NULL
INSERT INTO #Accounting(Field1, Field2)
VALUES (100.00, 50.00),
--Do the calculation and insert the result into a temp table
WHEN (Field2 - Field1) <= 0 THEN Field2 - Field1
END AS Field3,
WHEN (Field2 = Field1) THEN 0
END AS Field4
SELECT * FROM #AccountingTemp
--Use the temp table to update the main table.
SET a.Field3 = t.Field3,
a.Field4 = t.Field4
FROM #Accounting AS a
INNER JOIN #AccountingTemp AS t ON a.Id = t.Id
--Debug should be the same as previous debug
SELECT * FROM #Accounting
Keep in mind that the requires do not handle all possible states. Null is returned if for some reason Fields2 is greater than Field1. Negative values might produce unexpected results - not sure.
Play with the code if these cases are relevant.