Excel formula to SQL formula for query

[jeroenw]

Hi,

I have an Excel formula that I want to convert to an SQL formula so I can use it in a query.

The Excel formula is:

=D2*CEILING((B2-C2)/D2;1)

I thought I could use RoundUp, but that funcation is not available in SAP.

The formula in SAP looks like this:

t0.purpackun*RoundUp((T1.[Maxstock]-((t1.onhand-t1.iscommited)+ t1.onorder))/t0.purpackun,1) as 'Order quantity',

[alangolding25]

If you are looking for a basic SQL query to translate, try this : 

Select D * CEILING((B - C) / D ) AS result FROM your_table;

[RaymondGiuseppi]

Doesn't (recent versions of) Abap SQL handle a CEIL( sql_exp ) function?

[LoHa]

Hi

you can try this

SELECT 
 [OITM].[PurPackUn]
,[OITW].[Maxstock]
,[OITW].[OnHand]
,[OITW].[IsCommited]
,[OITW].[OnOrder]
,[OITM].[PurPackUn]*CEILING(([OITW].[Maxstock]-[OITW].[OnHand]-[OITW].[IsCommited]+[OITW].[OnOrder])/[OITM].[PurPackUn]) AS [OrderQty]
FROM 
OITM
INNER JOIN OITW ON OITW.ItemCode= OITM.ItemCode AND OITW.WhsCode = 'xx'
WHERE
OITM.ItemCode = 'xx'

regards Lothar

[Johan_Hakkesteegt]

Hi,

As it turns out, MS SQL has a function called CEILING.

SELECT CEILING(1.4/0.6)
/* result: 3 */

Regards,

Johan