# CASE STATEMENT Complications

• ### Question

• User1216627406 posted

Greetings experts,

I have a little complicated mathematical operations I am trying to do in SQL.

I have four field names, field1, field2, field3, field4.

Field1 contains a set amount, say \$100.00

By default, field2, field3 and field4 have 0 amount

If field1(\$100.00) is greater than field2, put the difference in field3.

Example 100-50 = 50. Difference is 50 and it goes to field3. This means that the user still has \$50.00 to pay.

100-100-0. 0 goes to field4 which means user is not owning any more money.

Field1 holds the original amount to be paid.

Field2 shows how much paid (or not paid)

Field3 shows how much still being owed.

Field4 shows 0 as amount paid up.

Sample:

```Field1                 Field2                    Field3                   Field4```

```\$100.00                \$50.00                   (\$50.00)                    -```

```\$100.00                \$100.00                  (\$0.00)                    \$0.00```

Am I approaching this the right way?

Friday, August 3, 2018 2:17 PM

### All replies

• User475983607 posted

#### simflex

Am I approaching this the right way?

Use standard accounting  principles (debit/credit) similar to a checkbox register.   Otherwise, you'll have maintenance nightmare on your hands.

```ID	UserId	Account	Amount	Date
1	1	Account	100	8/1/2018
2	1	Account	-50	8/2/2018```

To get the user's balance

```SELECT SUM(Amount)FROM AccountTable
WHERE UserId = 1```

Friday, August 3, 2018 2:47 PM
• User1216627406 posted

Thanks so much for your prompt response mgebhard.

However, your query doesn't match up with the sample you showed.

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.

Friday, August 3, 2018 3:19 PM
• User753101303 posted

Hi,

It's just SELECT Field1, Field2, Field1-Field2 FROM etc...

If you want to use two colums for display purpose and feel uncomfortable with case you could test the full expression for clarity ie :

SELECT Field1,Field2,
CASE WHEN Field1-Field2>0 THEN Field1-Field2 ELSE NULL END, -- this column is for positive values
CASE WHEN Field1-Field2<0 THEN Field1-Field2 ELSE NULL END -- this one is for negative values

Or it could be also done client side by exposing additional computed properties...

Edit: if you tried Something that fails it could be better to discuss directly your issue. The syntax for the CASE expression is at https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

Friday, August 3, 2018 3:39 PM
• User475983607 posted

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),
(100.00, 100.00),

--Do the calculation and insert the result into a temp table
SELECT	Id,
Field1,
Field2,
CASE
WHEN (Field2 - Field1) <= 0 THEN Field2 - Field1
END AS Field3,
CASE
WHEN (Field2 = Field1) THEN 0
END AS Field4
INTO #AccountingTemp
FROM #Accounting

--Debug
SELECT * FROM #AccountingTemp

--Use the temp table to update the main table.
UPDATE a
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.

Friday, August 3, 2018 4:22 PM
• User1216627406 posted

Thank you.

I didn't say that your original recommendation of following industry standard is wrong.

I am simply saying that the query you posted didn't match the sample code you posted.

Friday, August 3, 2018 6:43 PM
• User475983607 posted

I didn't say that your original recommendation of following industry standard is wrong.

I am simply saying that the query you posted didn't match the sample code you posted.

As far as I can see the SQL syntax is correct and matches the sample code.  Here's a runnable sample if you are interested in using rows rather than columns.

```IF OBJECT_ID('tempdb..#AccountTable') IS NOT NULL
DROP TABLE #AccountTable

CREATE TABLE #AccountTable(
ID		INT Identity(1,1),
UserId	INT,
Account	VARCHAR(64),
Amount	DECIMAL,
[Date]	DATETIME
)

INSERT INTO #AccountTable(UserId, Account, Amount, [Date])
VALUES	(1,	'Account',	100.00,	'8/1/2018'),
(1,	'Account',	-50.00,	'8/2/2018')

SELECT * FROM #AccountTable

SELECT SUM(Amount)
FROM #AccountTable
WHERE UserId = 1```

Friday, August 3, 2018 6:56 PM
• User753101303 posted

And the title is about CASE but you never shown a SQL statement using that. ;-)

I posted earlier a sample and a link to the doc. Then as pointed already it's common to show data the way they are stored but it happens also that they are best stored a way and best shown another way. So you have to "reshape" them as needed. So you would have two steps ie showing the current situation from the history you stored and then do simple calculation on reshaped data to present them on two distinct columns for clarity if this is what users want.

I assumed data were either already stored or shaped as you want for your final query.

Sometimes it's easier to just post what you tried and how it fails so that we can better understand the information you need to fix your issue. Which probkme do you have with the CASE expression (a common catch is to understand that it is an expression ie it returns a value).

Friday, August 3, 2018 7:26 PM
• User1216627406 posted

My apologies Patrice, for some reason, I missed your post.

Thank you.

I am reviewing both.

Saturday, August 4, 2018 3:41 AM
• User1216627406 posted

Sorry guys, I had a family issues that have kept me away from this.

I will review this tonight and come back with or without additional questions.