Solved

# Calculations within a SELECT statment

Posted on 2007-07-24
202 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

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 142

Accepted Solution

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 68

Expert Comment

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

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 142

Expert Comment

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

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

## Featured Post

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.