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.
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...
However, your query doesn't match up with the sample you showed.
I don't know what's wrong.
simflex
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.
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
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).
Member
247 Points
1112 Posts
CASE STATEMENT Complications
Aug 03, 2018 02:17 PM|simflex|LINK
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.00Am I approaching this the right way?
Thanks in advance
All-Star
42511 Points
18079 Posts
Re: CASE STATEMENT Complications
Aug 03, 2018 02:47 PM|mgebhard|LINK
Use standard accounting principles (debit/credit) similar to a checkbox register. Otherwise, you'll have maintenance nightmare on your hands.
To get the user's balance
Member
247 Points
1112 Posts
Re: CASE STATEMENT Complications
Aug 03, 2018 03:19 PM|simflex|LINK
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.
All-Star
45540 Points
16509 Posts
Re: CASE STATEMENT Complications
Aug 03, 2018 03:39 PM|PatriceSc|LINK
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
All-Star
42511 Points
18079 Posts
Re: CASE STATEMENT Complications
Aug 03, 2018 04:22 PM|mgebhard|LINK
I don't know what's wrong.
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 #AccountingKeep 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.
Member
247 Points
1112 Posts
Re: CASE STATEMENT Complications
Aug 03, 2018 06:43 PM|simflex|LINK
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.
All-Star
42511 Points
18079 Posts
Re: CASE STATEMENT Complications
Aug 03, 2018 06:56 PM|mgebhard|LINK
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 = 1All-Star
45540 Points
16509 Posts
Re: CASE STATEMENT Complications
Aug 03, 2018 07:26 PM|PatriceSc|LINK
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).
Member
247 Points
1112 Posts
Re: CASE STATEMENT Complications
Aug 04, 2018 03:41 AM|simflex|LINK
My apologies Patrice, for some reason, I missed your post.
Thank you.
I am reviewing both.
Member
247 Points
1112 Posts
Re: CASE STATEMENT Complications
Aug 09, 2018 08:47 PM|simflex|LINK
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.
I really appreciate your help.