Solved

calculation in select

Posted on 2006-06-21
7
1,420 Views
Last Modified: 2008-01-09
In sql server 2000 I have the following query in a stored procedure:
select  amount,
      amount * VAT / 100 as vatsum,
      amount + (amount * VAT / 100) as total
from table1

1. if the value of VAT is 0 then the value of total also become 0 although it is not 0 because it suppose to add the  
    amount. What is the problem here.
2. I wanted to write it like this
select        amount,
      amount * VAT / 100 as vatsum,
      amount + vatsum  as total
from table
but it is not accepted . what is the problem and how do I write it so that I write the calculation of vatsum only once?
Thankyou
Anat
0
Comment
Question by:ANAT2403
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
select  amount,
     amount * (VAT / 100) as vatsum,
     amount + (amount * (VAT / 100)) as total
from table1
0
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
Whether you write is as "amount + vatsum" or "amount + (amount...."  It would still calculate vatsum twice b/c vatsum is just an alias to that column.  SQL Server would need to calculate it twice.

you could re-write it to eliminate the multiple calculations.  Try the below query and see if it returns faster results.

select  amount,
     amount * VAT / 100 as vatsum,
     amount + (amount * VAT / 100) as total
from table1
where amount <> 0

UNION ALL select  0,
                           0,
                           0
from table1
WHERE amount = 0
0
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
if amount is indexed you may return faster results this way.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:ANAT2403
Comment Utility
nobody solved  the problem. the problem is not the fast of the query but that the total return 0 althogh in consists of
the amount which is not 0 and the vatsum which is 0
Can you help?
Anat
0
 

Author Comment

by:ANAT2403
Comment Utility
Ok  sorry, I found it. it's my mistake .
actually the VAT was written like this:
ISNULL(VAT, 0) and I didn't write it in the second part.
Sorry
Thankyou all
Anat
0
 
LVL 5

Accepted Solution

by:
rmacfadyen earned 500 total points
Comment Utility
Are you certain of your problem? With a VAT of 0 there I would be hugely surpised if the total as zero (amount + (amount * 0 / 100)). Here's a hardcoded sample that shows the basic math works as expected:

declare @Amount decimal(8,2)
declare @Vat int

set @Amount = 10.23
set @Vat = 7

select @Amount, round(@Amount * @Vat / 100, 2), round(@Amount + (@Amount * @Vat / 100), 2)

set @Amount = 10.23
set @Vat = 0

select @Amount, round(@Amount * @Vat / 100, 2), round(@Amount + (@Amount * @Vat / 100), 2)

Are you perhaps not showing your full query? If you are not... have you considered how NULL will affect numeric calculations (SUM() and so forth)?

Regards,

Rob
0
 

Author Comment

by:ANAT2403
Comment Utility
Thankyou you are write as you can see in my previuos answer I didn't show the complete query and there was the
problem that I already solved.
Anat
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now