SQL 2008 Mathmatical Query Help

[BubbaG]

Need some help. I am trying to round a number in a query to the nearest 500 and then perform a few more mathmatical functions. Basically I need to take 'r.consideration' value to the nearest 500 then subtract 500, then multiply that number by .0016

I use the round statement below to get it to the nearest 500, but when I try to add the additional math it doesn't work correctly.

Round (r.consideration*2,-3)/2 AS 'Revenue Tax'

Any thoughts. Thanks!

 

[gmmastros]

What is the data type of the consideration column? Can you show us some sample values that don't work correctly?

If you're not sure how to get the data type...

Select Data_Type, Numeric_Precision, Numeric_Scale
From   Information_Schema.Columns 
Where  Table_Name = '[!]YourTableNameHere[/!]' 
       And Column_Name = 'consideration'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BubbaG]

The data type is 'money'.

SELECT C.DocID,C.KindCode, C.FileNumber,C.Book, C.Page, c.FileDate, c.InstrumentDate,Round (((r.consideration*2,-3)/2)-500)*.0016 AS 'Revenue Tax'
FROM Constant C, ReceiptTransactions R
WHERE c.RecordingNum = r.RecordingNum
AND c.FileDate >='2010-02-10 00:00:00.000' and c.FileDate <= '2010-02-10 23:59:59.998'

 

[bborissov]

Too much brackets
Maybe it should be like this:

((Round(r.consideration*2,-3) / 2 )-500)*.0016

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Boris's advice should take care of the calculations. Can I offer another suggestion?

When you are filtering a date to return a single day, you should not use:

[tt][blue]AND c.FileDate >='2010-02-10 00:00:00.000' and c.FileDate <= '2010-02-10 23:59:59.998'
[/blue][/tt]

Instead, you should do this:

AND c.FileDate >='2010-02-10 00:00:00.000' and c.FileDate [!]< '2010-02-11 00:00:00.000' [/!]

This is a minor point, but the version I am suggesting will "future proof" your code. With a DateTime data type, your end time 23:59:59.998 will round to 23:59:59.99[!]7[/!]. If you ever use this code on a SmallDateTime data type, your end time will round up to the next day, and you could possibly get the wrong data. Additionally, SQL Server 2008 provides for some newer DateTime data types with varying precisions. If you use my suggestion, you will never have a problem regardless of the actual data type.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BubbaG]

That worked! Thanks for your help with this and your advice!