locked
CASE STATEMENT Complications RRS feed

  • 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?

    Thanks in advance

    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.

    I really appreciate your help.

    Thursday, August 9, 2018 8:47 PM