CODEWITHSUNDEEP
sqlsql-server
Crezzer7
Crezzer7

Reputation: 2335

Multiple Calculations in CASE Statement SQL

I am trying to get the following SQL Code to work however I am struggling with finding out how to do multiple calculations with a CASE Statement, here is the Code:

SELECT 
dbo.Table1.PrimaryKeyID,
  CASE

       WHEN (number1 >0 AND number2 = 0) 
             THEN number1 = number1 + CalcView.Quantity

       WHEN (number1 =0 AND number2 <=0) 
             THEN number3 = number3 + CalcView.Quantity,
                  number2 = number2 - CalcView.Quantity

       WHEN (number1 =0 AND number2 >0) 
             THEN number3 = number3 + number2 
                  number1 = number1 + (CalcView.Quantity - number2)
                  number2 = 0
END
FROM Table1
INNER JOIN CalcViewON Table1.PrimaryKeyID= CalcView.PrimaryKeyID
WHERE Table1.PrimaryKeyID= CalcView.PrimaryKeyID

As you can see I have just put the calculations within the THEN at the moment, please could anyone point me in the right direction of how to get this statement to work? thankyou for any help you can give me

Examples:

Case 1
Number1 = 5
Number2 = 0
CalcView.Quantity = 3

5 = 5 + 3 (8)


Case 2
Number 1 = 5
Number 2 = 3
Number 3 = 2
CalcView.Quantity = 3

2 = 2 + 3 (5)
3 = 3 - 3 (0)


Case 3
Number 1 = 5
Number 2 = 3
Number 3 = 2
CalcView.Quantity = 6

2 = 2 + 3 (5)
1 = 1 + (6 – 3) (4)
Number2 = 0

the aim: to update number1, number2 and number3 based on the 3 WHEN statements. currently I am using a select statement to make sure I am achieving the correct outputs...

What alternatives are there to CASE statements that could help me achieve my goal? Thanks Again

Upvotes: 0

Views: 3820

Answers (1)

Jamiec
Jamiec

Reputation: 136094

You are trying to calculate number1, number2 and number3 using a single CASE...WHEN...END statement, this will not work - you need 3 distinct case statements.

If I understand your logic, this should do it

SELECT 
dbo.Table1.PrimaryKeyID,
  CASE
    WHEN (number1 >0 AND number2 = 0) 
       THEN number1 + CalcView.Quantity
    WHEN  (number1 =0 AND number2 >0) 
       THEN number1 + (CalcView.Quantity - number2)
    ELSE
       number1
  END AS number1,
  CASE 
    WHEN(number1 =0 AND number2 <=0) 
       THEN number2 - CalcView.Quantity
    WHEN  (number1 =0 AND number2 >0) 
       THEN 0
    ELSE
       number2
  END AS number2,
  CASE
    WHEN (number1 =0 AND number2 <=0) 
       THEN number3 + CalcView.Quantity
    WHEN  (number1 =0 AND number2 >0) 
       THEN number3 + number2 
    ELSE
       number3
  END AS number3
FROM Table1
INNER JOIN CalcViewON Table1.PrimaryKeyID= CalcView.PrimaryKeyID
WHERE Table1.PrimaryKeyID= CalcView.PrimaryKeyID

Upvotes: 2

Related Questions