No Sale ''turn over''

[Former Member]

hai Expert

how to write query that displays

my concern is if the report only submit only base on invoiced items.. it might give complete figure for all available stock ... Can you put "No Sale" on Turn Over Coloum if the sales is zero but has stock on hand.

SELECT T0.ItemCode, T0.Dscription,T1.OnHand, 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'

,(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())

AND (T0.BaseQty*T1.NumInSale)>0

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

ORDER BY T0.ItemCode

thaks for all

[Former Member]

hai Expert

how to write query that displays

my concern is if the report only submit only base on invoiced items.. it might give complete figure for all available stock ...

1. Can you put "No Sale" on Turn Over Coloum if the "sales is zero" but has stock on hand. AND

2. Can you put "Sale" on Turn Over Coloum if the "sales" have stock of the has stock on hand

SELECT T0.ItemCode, T0.Dscription,T1.OnHand, 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' ,

(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())

AND (T0.BaseQty*T1.NumInSale)>0

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

ORDER BY T0.ItemCode

thaks for all

[Former Member]

Hi , Please try this and let me know the status

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

[Former Member]

Hi Consultant thanks very much

Yes, I really need this Query

What is "Union" function for?..

why Qty Sales and Turn Over have alot of decimal numbers

(it should be 2 digit decimal only) and the Qty Sales vary with the amount of sales analysis report

I wish to include A / R Credit Memo also, how to use the Query?

Thanks a lot

[Former Member]

close theard