Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error in Calculating Floor() value in SQL Expand / Collapse
Author
Message
Posted Monday, September 5, 2011 9:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 23, 2012 10:55 PM
Points: 1, Visits: 18
Hi all,

I need to calculate numbers of money notes needed for salary preparation.
I have problem with the last column, i.e. USD1. The value return is 3 instead of 2.
SELECT FLOOR(2.78) ==> return 2.

I am not sure why my following code return 3.

DECLARE @GrossPay Decimal(12,2)
SET @GrossPay = 132.78
SELECT @GrossPay,
FLOOR(@GrossPay/100) AS USD100,
FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50) AS USD50,
FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20) AS USD20,
FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50 - (FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20))*20)/10) AS USD10,
FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50 - (FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20))*20 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50 - (FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20))*20)/10))*10)/5) AS USD5,
FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50 - (FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20))*20 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50 - (FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20))*20)/10))*10-(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50 - (FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20))*20 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50 - (FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100 -(FLOOR((@GrossPay - FLOOR(@GrossPay/100)*100)/50))*50)/20))*20)/10))*10)/5))*5)/1) AS USD1


Hope that anyone can help me with this.

Thanks in advance!
Post #1170107
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse