Question Re: Excel formula to SQL formula for query in Technology Q&A

Excel formula to SQL formula for query

jeroenw — Thu, 30 May 2024 08:26:11 GMT

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',

Re: Excel formula to SQL formula for query

Johan_Hakkesteegt — Thu, 30 May 2024 12:56:05 GMT

Hi,

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

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

Regards,

Johan

Re: Excel formula to SQL formula for query

LoHa — Thu, 30 May 2024 13:19:51 GMT

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

Re: Excel formula to SQL formula for query

RaymondGiuseppi — Thu, 30 May 2024 14:34:12 GMT

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

Re: Excel formula to SQL formula for query

alangolding25 — Thu, 30 May 2024 18:08:50 GMT

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

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