Skip to Content

Stock Value between two dates SAP B1 Query

it works but it doesn't give all items only for transaction between two dates. How can i see with all items?

Thank you

SELECT T0.ItemCode, T1.ItemName, sum(T0.InQty - T0.OutQty) as 'On Hand', ( sum(T0.InQty - T0.OutQty)*T1.[AvgPrice]) 'Total Price', T2.ItmsGrpNam

FROM OINM T0

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OITB  T2 ON T1.[ItmsGrpCod] = T2.[ItmsGrpCod]

WHERE T0.DocDate >= [%0] and T0.DocDate <= [%1] AND T0.ItemCode NOT LIKE '%%A999999%%' AND T0.ItemCode NOT LIKE '%%SAR101%%' AND T0.ItemCode NOT IN ('a-item') AND T2.ItmsGrpNam LIKE '%[%test%]%'

GROUP BY T0.ItemCode, T1.ItemName, T1.[AvgPrice], T2.ItmsGrpNam
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Oct 14, 2019 at 12:08 PM

    If I understood you correctly, you should delete part with dates from the query

    SELECT T0.ItemCode, T1.ItemName,sum(T0.InQty - T0.OutQty)as'On Hand',(sum(T0.InQty - T0.OutQty)*T1.[AvgPrice])'Total Price', T2.ItmsGrpNam
    FROM OINM T0
    INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
    INNER JOIN OITB  T2 ON T1.[ItmsGrpCod]= T2.[ItmsGrpCod]
    WHERE T0.ItemCode NOT LIKE '%%A999999%%' 
    AND T0.ItemCode NOT LIKE '%%SAR101%%' 
    AND T0.ItemCode NOT IN ('a-item') 
    AND T2.ItmsGrpNam LIKE '%[%test%]%' 
    GROUP BY T0.ItemCode, T1.ItemName, T1.[AvgPrice], T2.ItmsGrpNam
    
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 14, 2019 at 12:18 PM

    Thank you for answer. if i delete dates. How can i see example last month my stock value? i can't choose date

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 14, 2019 at 01:10 PM

    I'm not sure which DB you use but adding something like

    AND T0.DocDate >= '2019-10-01' AND T0.DocDate <= '2019-10-31'

    or whatever date you prefer before GROUP BY, should do the trick.

    You can't see ALL dates and LAST MONTH dates at the same time (unless you make UNION).

    Add a comment
    10|10000 characters needed characters exceeded

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.