0
Former Member
May 06, 2009 at 09:06 AM

# TurnOver Days

16 Views

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