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

x
?
Solved

Calculations within a SELECT statment

Posted on 2007-07-24
6
Medium Priority
?
203 Views
Last Modified: 2010-03-19
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
Comment
Question by:g-spot
6 Comments
 
LVL 11

Expert Comment

by:fanopoe
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

by:
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

by:Scott Pletcher
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:g-spot
ID: 19558794
Thank you all for your answers.

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

by:Guy Hengel [angelIII / a3]
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

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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question