CODEWITHSUNDEEP
mysqlsqlexcelexcel-formula
David
David

Reputation: 1

Convert Excel Max (Ceiling) function to Sql Query

I have to get the same results in SQL as in Excel. The function in Excel is:

=MAX(CEILING(((D2*B2))+1;10)-1;0)+0.99

I have tried the following in SQL and the query works, but with different result:

IF ([CSV_COL(6)] > 1000,Round((ceiling([CSV_COL(6)] * 1.0354+6)+0.99)/1.14,2), Round((ceiling([CSV_COL(6)] * 1.070+6)+0.99)/1.14,2))

Please note:

CSV_COL (6) is Excel D2

1.0354 is Excel B2

The 1.14 is just to get the amount VAT Excel.

The 2 is to get 2 decimals.

Example Result:

R1078.00 needs to floor or ceiling to R1079.99
or
R1063.24 needs to floor or ceiling to R1069.99

I basically need to convert the Excel query above to SQL format as current SQL query above.

Upvotes: 0

Views: 1064

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

Because the SQL ceiling function just rounds up to the next integer, you have to use the workaround of dividing by 10, then rounding up, then multiplying by 10 e.g. when your D2 is 1140:-

select ceiling(1140 * 1.0354/10)*10-.01

Result

(No column name)
1189.99

Upvotes: 0

Related Questions