[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Calculations within a SELECT statment

Posted on 2007-07-24
Medium Priority
203 Views
I need to calculate a gross price and percentage element of a price using a select statement.

I want to take the Price from one table and then calculate the price before VAT and the VAT from that price.

Something like the query below. The first part of the SET statement gets the price before VAT (Expr1) and the second part subtracts Expr1 from the original price to get the VAT

UPDATE       Policies
SET                Price = (SELECT ROUND(100 / (117.5) * Details.Price, 2) AS Expr1)
Tax = (SELECT Details.Price - Expr1)
FROM            Policies INNER JOIN
Details ON Policies.PolicyGUID = Details.PolicyGUID
0
Question by:g-spot

LVL 11

Expert Comment

ID: 19558162
try this:
UPDATE       Policies
SET                Price = (SELECT ROUND(100 / (117.5) * Details.Price, 2) AS Expr1)
Tax = (SELECT Details.Price - (SELECT ROUND(100 / (117.5) * Details.Price, 2) )
FROM            Policies INNER JOIN
Details ON Policies.PolicyGUID = Details.PolicyGUID

since expr1 has not been updated in the transaction yet, you'll need to use the calculation
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19558165

UPDATE       Policies
SET     Price = ROUND(100 / (117.5) * d.Price, 2)
Tax =  d.Price - ROUND(100 / (117.5) * d.Price, 2)
FROM Policies p
JOIN  Details d
ON p.PolicyGUID = d.PolicyGUID
0

LVL 70

Expert Comment

ID: 19558581
You can also do this, which is sometimes easier to follow and makes later changes less difficult:

DECLARE @PriceBeforeVat DECIMAL(..., ...) --<<-- set to same datatype as Price on table Policies

UPDATE       Policies
SET             @PriceBeforeVat = ROUND(100 / (117.5) * Details.Price, 2),
Price = @PriceBeforeVat,
Tax = Details.Price - @PriceBeforeVat
FROM            Policies INNER JOIN
Details ON Policies.PolicyGUID = Details.PolicyGUID
0

Author Comment

ID: 19558794

Where 3 answers are given am I expected to test all 3 then give the mark to the ones that are correct?

Or can i just got for the answer that most appeals to me. I'd have to say Angellll's answer seems to be the most simple and easy to use. It also works.

Would I be wrong to award all the points to him even though fanopoe answered first (although I havent tested that answer)
0

LVL 143

Expert Comment

ID: 19558871
AFAIK, fanopoe's syntax is plain wrong.
Scottpletchers suggestion is a very nice trick to avoid multiple calculations and formula change problems.
you can of course split points as you wish.
0

Author Comment

ID: 19564295
Im going to award all points to Angellll as that was the solution I used.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down ā¦
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month18 days, 22 hours left to enroll