TurnOver Days

[Former Member]

Hi expert,

how to use Query, i want to include Turn over Days in this my Query

where

Turn Over Days= TURN OVER IN MONTH * 30

SET ARITHABORT OFF SET ANSI_WARNINGS OFF

SELECT T0.ItemCode, T0.Dscription,T1.OnHand, convert(varchar,SUM(T0.BaseQty*T1.NumInSale)) AS 'Qty Sales',DAY(GetDate()) as 'Days',

Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END AS 'Total days'

,convert (varchar,(t1.onhand/(SUM(T0.BaseQtyT1.NumInSale)/DAY(GetDate()) Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END)))AS 'TURN OVER IN MONTH'

FROM DBO.INV1 T0 INNER JOIN DBO.OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE Month(T0.DocDate) = Month(GetDate()) AND Year(T0.DocDate) = Year(GetDate())

GROUP BY T0.ItemCode,T0.Dscription,T1.OnHand

union

SELECT T1.ItemCode, T1.itemname,T1.OnHand, '' AS 'Qty Sales',DAY(GetDate()) as 'Days',

Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END AS 'Total days'

,'No Sales' AS 'TURN OVER IN MONTH'

FROM DBO.OITM T1

WHERE T1.ITEMCODE NOT IN (SELECT ISNULL(ITEMCODE,0) FROM INV1)

GROUP BY T1.ItemCode,T1.itemname,T1.OnHand

ORDER BY 1

thanks for yor replay..

[Former Member]

Hi... Try This..

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF 

SELECT T0.ItemCode, T0.Dscription,T1.OnHand, convert(varchar,SUM(T0.BaseQty*T1.NumInSale)) AS 'Qty Sales',DAY(GetDate()) as 'Days', 
Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END AS 'Total days' 
,convert (varchar,(t1.onhand/(SUM(T0.BaseQty*T1.NumInSale)/DAY(GetDate())* Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END)))AS 'TURN OVER IN MONTH'
,round(convert (varchar,(t1.onhand/(SUM(T0.BaseQty*T1.NumInSale)/DAY(GetDate())* Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END))),4)* 30 AS 'Turn Over Days'

FROM DBO.INV1 T0 INNER JOIN DBO.OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE Month(T0.DocDate) = Month(GetDate()) AND Year(T0.DocDate) = Year(GetDate())
GROUP BY T0.ItemCode,T0.Dscription,T1.OnHand 


union



SELECT T1.ItemCode, T1.itemname,T1.OnHand, '' AS 'Qty Sales',DAY(GetDate()) as 'Days', 
Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END AS 'Total days' 
,'No Sales' AS 'TURN OVER IN MONTH'
,NULL
FROM DBO.OITM T1 
WHERE T1.ITEMCODE NOT IN (SELECT ISNULL(ITEMCODE,0) FROM INV1)
GROUP BY T1.ItemCode,T1.itemname,T1.OnHand 
ORDER BY 1

Hope this helps

[Former Member]

Hi Expert,

thanks for your replay my Question and your Query Work,

but I have a question for you, what the function of the number 4 in the Query, the time before the 30AS 'Turn Over Days'

,round(convert (varchar,(t1.onhand/(SUM(T0.BaseQtyT1.NumInSale)/DAY(GetDate()) Case WHEN Month(GetDate()) in (1,3,5,7,8,10,12) THEN 31 WHEN Month(GetDate()) in (2,4,6,9,11) THEN 30 ELSE 28 END ))),4) *30 AS 'Turn Over Days'

Thanks a lot..,

Edited by: rachelhel on May 7, 2009 8:09 AM

Edited by: rachelhel on May 7, 2009 9:05 AM

[Former Member]

Hi.... Please observe that there is one more function round at begining of the line which will round the value of the turnover to 4 decimal point. I have used this function because the turnover output is too long (ex: 0.154232156497933) and while calculating the turnover days there is a error message Hope this clears your dobut. Regards

[Former Member]

Hi Expert....

I mean, at the Turn overDays

(THEN 30 ELSE 28 END))), 4) * 30 AS 'Days Turn Over')

(in the search for Query turnover Days there is a number 4, and what the function number)

whether the turnover can be changed

Turnover and QtySales of output is too long (eg, 0.154232156497933) to (eg, 0.15) are only two decimal

thanks very much

[Former Member]

Hi, Yes you can change from 0.154322689222 to 0.15 by using round function Thanks

[Former Member]

Hi,

thanks..., but i don't know this change from 0.154322689222 to 0.15 by using round function

because I have tried many times to change but can not, can you help me to change again,

thanks a lot,

[Former Member]

Hi consulatant SAP B1

hi plss...

Can you or anyone else who can help experts to solve my problem.

I want to include table one month-1

example:

For example this month is on May 25, so I want to show the Quantity of one month April 25

thanks