0

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.

rtruszk
  • 3,902
  • 13
  • 36
  • 53
David
  • 1
  • What is the value of `D2`and `B2` columns in your example results? – Kamil Gosciminski Apr 15 '15 at 09:03
  • Thank you for asking. If I use 1000 and 1.03540 the excel formula above will give me a result of R1189.99 and R1043.85 VAT Excl. in the next column where I just take the result and divide it by 1.14. I need the sql query to give me the same result: R1189.99 VAT Incl. or R1043.85 VAT Exclusive. – David Apr 15 '15 at 09:29
  • Here is a link of the screen-print from Excel. As you can see the formula is like that and it works. http://tinypic.com/r/9zt7it/8 – David Apr 15 '15 at 10:16

1 Answers1

0

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
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Good! This is useful too if you want to display the final result to 2 dec pl:- http://stackoverflow.com/questions/20119282/how-to-display-two-digits-after-decimal-in-sql-server – Tom Sharpe Apr 15 '15 at 12:57