Error in calculating Floor() from SQL

I need to calculate numbers of money notes needed for salary preparation. I have problem with the last column, i.e. USD1.

I will to get the value for USD1 = 2.

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))*开发者_JS百科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

Sorry for such a long query.

You can copy and paste the above Sql Query and execute it, then you will understnad what i mean. Hope that anyone can help me with this.

Thanks in advance!

A lot of your expressions can be simplified if you realise you don't need to subtract the calculated previous amounts (the only one I can't get a decent expression for at the moment is USD10). For instance, the USD1 column is always going to be a value between 0 and 4, based on the remainder of dividing the whole amount by 5.

That's the definition of the modulo operator, in SQL this is %, so we can have:

(CONVERT(int,@GrossPay)%5) as USD1

As I say, most of the others can also be calculated by using modulo (except USD10, still trying to think of a simplification for that). I've also introduced another variable, called @GrossPayInt, because it handles the rounding automatically:

DECLARE @GrossPay Decimal(12,2) SET @GrossPay = 132.78
DECLARE @GrossPayInt int SET @GrossPayInt = @GrossPay

SELECT @GrossPay,
    @GrossPayInt/100 as USD100,
    (@GrossPayInt%100)/50 as USD50,
    (@GrossPayInt%50)/20 as USD20,
    CASE WHEN (@GrossPayInt%100)/10 IN (1,3,6,8) THEN 1 ELSE 0 END as USD10 as USD10,
    (@GrossPayInt%10)/5 as USD5,
    (@GrossPayInt%5) as USD1

If anyone else can suggest a good short expression for USD10, I think that this is done.

Consider using ROUND:

SELECT ROUND(2.78,0)

Alternatively, adding 0.5 will make floor round up instead of floor:

SELECT FLOOR(2.78+0.5)