Skip to Content
author's profile photo Former Member
Former Member

TurnOver Days

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on May 06, 2009 at 09:57 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.