best way to do calculations in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

best way to do calculations in query

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-03-06 : 17:23:26

Hi,
I need to perform a number of mathimatical calculations in a query (will be saved as a sproc) and I'm not sure the best way to do it.

Is there a way to save parts of the calculations as variables so that I can use the variable throughout the rest of the query? If you look at the example below TotalPremium = (Round(SUM(a.Premium),c.[Round])

I use this value in a couple of places. It would be nice to have a variable named "TotalPremium" instead of copying the calculation. Is there a good way to do this? It seems like it would be best to do the calculation in the sproc as opposed to outputing the raw data and using another language (C#) to calculate the values. Is this correct?

These calculations can get quite long and unreadable, I'm just curious if there is a better way to write theses types of calculations.

Thanks

CREATE TABLE RatePaymentPlans (
[PaymentPlanID] [int],
[CompanyID] [int],
[PolicyFee] [float],
[InstallmentFee] [float],
[DownPayPercent] [float],
[Installments] [int],
[Round] [int],
)
GO
INSERT INTO RatePaymentPlans VALUES (1,1,0,0,50,1,0)
GO

CREATE TABLE QuotePremiums (
[PremiumID] [int],
[QuoteID] [int],
[CompanyID] [int],
[VehicleDriverNumber] [int],
[Coverage] [varchar] (10),
[Premium] [float]
) ON [PRIMARY]
GO
INSERT INTO QuotePremiums VALUES (1,99,1,1,'COMP',500)
GO
INSERT INTO QuotePremiums VALUES (2,99,1,1,'COLL',250)
GO
INSERT INTO QuotePremiums VALUES (3,99,1,1,'MED',175)
GO

CREATE TABLE QuotePaymentPlans (
QuoteID [int],
PaymentPlanID [int]
)
GO
INSERT INTO QuotePaymentPlans VALUES (99,1)
GO

SELECT
a.CompanyID
,a.VehicleDriverNumber
,(Round(SUM(a.Premium),c.[Round]) * (c.DownPayPercent/100) + c.PolicyFee) AS DownPayment
,((Round(SUM(a.Premium),c.[Round]) - (Round(SUM(a.Premium),c.[Round]) * (c.DownPayPercent/100) + c.PolicyFee))/c.Installments) + c.InstallmentFee As Remainder
,c.Installments
FROM
QuotePremiums a INNER JOIN QuotePaymentPlans b ON a.QuoteID = b.QuoteID
INNER JOIN RatePaymentPlans c ON b.PaymentPlanID = c.PaymentPlanID
WHERE
a.QuoteID = 99
GROUP BY
a.CompanyID
,a.VehicleDriverNumber
,c.[Round]
,c.DownPayPercent
,c.PolicyFee
,c.Installments
,c.InstallmentFee

Nic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-03-06 : 17:27:56

I *think* SQL server sorta does that "storing in variable" for you. I've done some things along the same lines, and it was jsut as fast as trying to store it in a variable.

I'd just leave it be.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

nic
Posting Yak Master

209 Posts

Posted - 2003-03-06 : 17:40:00

Thanks but I'm more worried about having unreadable/unmanagable code. These calculations keep on building on each other and having to re-calculate each value from scratch raises the possibility of errors etc. Speed is important, but if is doesn't completely slow down the process is there a way to store segments of the calculations as variables so I can use it in other parts of the calculations?

Nic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-06 : 17:49:29

Not really, other than using a temp table or table variable. You can pick a certain intermediate calculation, run a query that does it, and put the results into the temp table. Then you'd join the temp table to the other tables to perform the remaining calculations, but using the temp table column instead of the original calculation expression.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-06 : 18:15:23

Do you mean something like this?

If the expression (A + B+ C+ D)/E is repeated many times, instead of:

SELECT ((A + B + C + D) /E) + 1, ((A + B + C + D) /E) - 1, ((A + B + C + D) /E) * 2, ((A + B + C + D) /E) * 3
FROM
Table

you can do :

SELECT Calc + 1, Calc - 1, Calc * 2, Calc * 3
FROM
(SELECT (A + B + C + D) /E as Calc FROM Table) A

not sure if that is what you are after, but it makes the code much more readable. Might be more efficient, too, because Calc is only evaluated once.

- Jeff

Edited by - jsmith8858 on 03/06/2003 18:16:22

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-06 : 21:01:59

A perfect example for using SQL Server User Defined Functions.

Edited by - ValterBorges on 03/06/2003 21:03:33

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-07 : 07:34:24

My $.02 :

I'd say a UDF is good if multiple objects in your database are using the same calculation ... but if it's just one query that needs to reference a calculation many times (as in my example), it might be cleaner and easier to read by using a sub-query -- and/or the UDF as required.

That is,

SELECT dbo.MyUDF(A,B,C,D,E) + 1, dbo.MyUDF(A,B,C,D,E) - 1, dbo.MyUDF(A,B,C,D,E) * 2, dbo.MyUDF(A,B,C,D,E) * 3
FROM
Table

is still a bit harder to read than:

SELECT Calc + 1, Calc - 1, Calc * 2, Calc * 3
FROM
(SELECT dbo.MyUDF(A,B,C,D,E) as Calc FROM Table) A

And if this one stored proc or view is the only one that uses the MyUDF() function, then I'd say it may be better just to list out the formula in the subquery ... but if mutiple views or procs use that calculation (or may in the future), go ahead and create a UDF.

Either way, nic, hopefully this gives you the answer(s) you need!

- Jeff

nic
Posting Yak Master

209 Posts

Posted - 2003-03-07 : 15:14:10

Thanks for all your help. Using subqueries is very helpful. Quick question though. Is the subquery a complete tsql statement? (i.e. does it contain all the joins and input parameter or does it somehow join to the main statement?)

In my original statement,"(Round(SUM(a.Premium),c.[Round])" is used a number of times. Would this be the subquery?

SELECT (Round(SUM(a.Premium),c.[Round]) AS Calc
FROM
QuotePremiums a INNER JOIN QuotePaymentPlans b ON a.QuoteID = b.QuoteID
INNER JOIN RatePaymentPlans c ON b.PaymentPlanID = c.PaymentPlanID
WHERE
a.QuoteID = 99
Group By c.[Round]

or would I somehow join the subquery to the main statement so I wouldn't have to provide the quoteID value in the sub and main query.

Sorry, I'm just having trouble applying everything. Thanks for all your help.

Nic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-07 : 15:30:35

No joins; you are just putting your entire SQL into a subquery and selecting everything from that subquery. All filters and joins live in the subquery.

In your case, I think you would have something like:

SELECT A.* ,
(RoundedPremium* DownPayPct + PolicyFee) AS DownPayment,
(RoundedPremium- (RoundedPremium* DownPayPct + PolicyFee))/ Installments) + InstallmentFee As Remainder

FROM
(
SELECT a.CompanyID
,a.VehicleDriverNumber, Round(SUM(a.Premium),c.[Round]) as RoundedPremium, c.downpaypercent /100 as DownPayPct , c.investmentfee, c.installments, c.policyfee
FROM
QuotePremiums a INNER JOIN QuotePaymentPlans b ON a.QuoteID = b.QuoteID
INNER JOIN RatePaymentPlans c ON b.PaymentPlanID = c.PaymentPlanID
WHERE
a.QuoteID = 99
GROUP BY
a.CompanyID
,a.VehicleDriverNumber
,c.[Round]
,c.DownPayPercent
,c.PolicyFee
,c.Installments
,c.InstallmentFee
) A

Note I put DownPaymentPercent / 100 as a formula in the subquery as well as the rounded premium formula.

See how it gives you a nice two-step look at your process? first you get the data you need and make two main calculations, and then you query all of THAT and do some math and return your final results. Hopefully easier to read. Play around, you'll get the hang out it. Use a simplier query (like my (A+B+C+D)/E example) to play with and learn from.

I've learned never to try out new techniques on big, complex real-world things -- always start small.

- Jeff

Edited by - jsmith8858 on 03/07/2003 15:33:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources