Skip to Content

Query Help

Hi All

SELECT TOP (100) PERCENT a.Warehouse, a.ItemCode, a.ItemName, SUM(a.OpeningBalance) AS OpeningBalance, SUM(a.INq) AS INq, SUM(a.OUTq) AS OUTq, SUM(a.price)

AS Price, SUM(a.OpeningBalance) + SUM(a.INq) - SUM(a.OUTq) AS Closing

FROM dbo.OITM AS I1 LEFT OUTER JOIN

(SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, SUM(N1.InQty) - SUM(N1.OutQty) AS OpeningBalance, 0 AS INq,

0 AS OUTq

FROM dbo.OINM AS N1 INNER JOIN

dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode

WHERE (N1.DocDate < '01/apr/2011')

GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName

UNION ALL

SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, 0 AS OpeningBalance, SUM(N1.InQty) AS INq, 0 AS OUTq

FROM dbo.OINM AS N1 INNER JOIN

dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode

WHERE (N1.DocDate >= '01/apr/2011') AND (N1.DocDate <= '20/dec/2011') AND (N1.InQty > 0)

GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName

UNION ALL

SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, 0 AS OpeningBalance, 0 AS INq, 0 AS OUT

FROM dbo.OINM AS N1 INNER JOIN

dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode

WHERE (N1.DocDate >= '01/apr/2011') AND (N1.DocDate <= '20/dec/2011') AND (N1.InQty = 0) AND (N1.OutQty = 0)

GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName

UNION ALL

SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, 0 AS OpeningBalance, 0 AS INq, SUM(N1.OutQty) AS OUTq

FROM dbo.OINM AS N1 INNER JOIN

dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode

WHERE (N1.DocDate >= '01/apr/2011') AND (N1.DocDate <= '20/dec/2011') AND (N1.OutQty > 0)

GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName) AS a ON a.ItemCode = I1.ItemCode

GROUP BY a.Warehouse, a.ItemCode, a.ItemName

ORDER BY a.ItemCode

This is giving me output.

In opening stock part I want current FINANCIAL YEAR

AND IN QTY AND OUT QTY PART I WANT CURRENT FINANCIAL YEAR TILL DATE

AND I WANT TO CONVERT IT INTO WAREHOUSE WISE AND NOT ITEM WISE

pLEASE HELP ME

THANKS

MALHAAR

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Dec 27, 2011 at 11:56 AM

    Hi,

    Check this and see if the same caters to your needs :

    SELECT TOP (100) PERCENT a.Warehouse, a.ItemCode, a.ItemName, SUM(a.OpeningBalance) AS OpeningBalance, SUM(a.INq) AS INq, 
    SUM(a.OUTq) AS OUTq, SUM(a.price) 
    AS Price, SUM(a.OpeningBalance) + SUM(a.INq) - SUM(a.OUTq) AS Closing
    FROM dbo.OITM AS I1 LEFT OUTER JOIN
    (SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, SUM(N1.InQty) - SUM(N1.OutQty) AS OpeningBalance,
    0 AS INq, 
    0 AS OUTq
    FROM dbo.OINM AS N1 INNER JOIN
    dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode
    WHERE (N1.DocDate >= '01/apr/2011') AND (N1.DocDate <= '20/dec/2011')
    GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName
    UNION ALL
    SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, 0 AS OpeningBalance, SUM(N1.InQty) 
    AS INq, 0 AS OUTq
    FROM dbo.OINM AS N1 INNER JOIN
    dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode
    WHERE (N1.DocDate >= '01/apr/2011') AND (N1.DocDate <= '20/dec/2011') AND (N1.InQty > 0)
    GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName
    UNION ALL
    SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, 0 AS OpeningBalance, 0 AS INq, 
    0 AS OUT
    FROM dbo.OINM AS N1 INNER JOIN
    dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode
    WHERE (N1.DocDate >= '01/apr/2011') AND (N1.DocDate <= '31/dec/2011') AND (N1.InQty = 0) AND (N1.OutQty = 0)
    GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName
    UNION ALL
    SELECT N1.Warehouse, N1.ItemCode, I1.ItemName, SUM(N1.TransValue) AS price, 0 AS OpeningBalance, 0 AS INq, 
    SUM(N1.OutQty) AS OUTq
    FROM dbo.OINM AS N1 INNER JOIN
    dbo.OITM AS I1 ON I1.ItemCode = N1.ItemCode
    WHERE (N1.DocDate >= '01/apr/2011') AND (N1.DocDate <= '31/dec/2011') AND (N1.OutQty > 0)
    GROUP BY N1.Warehouse, N1.ItemCode, I1.ItemName) AS a ON a.ItemCode = I1.ItemCode
    GROUP BY a.Warehouse, a.ItemCode, a.ItemName
    ORDER BY a.Warehouse
    

    Kind Regards,

    Jitin

    SAP Business One Forum Team

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 28, 2011 at 04:40 AM

    Dear Malhar,

    Try this its help you.

    Declare @FromDate Datetime

    Declare @ToDate Datetime

    Declare @Whse nvarchar(10)

    select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'

    select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'

    select @Whse = Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '[%2]'

    Select @Whse as 'Warehouse', a.Itemcode, max(a.Dscription) as ItemName,

    sum(a.OpeningBalance) as OpeningBalance, sum(a.INq) as 'IN', sum(a.OUT) as OUT,

    ((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing ,

    (Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM

    from( Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))

    as OpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1

    Where N1.DocDate < @FromDate and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,

    N1.Dscription Union All select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,

    sum(N1.inqty) , 0 as OUT From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate

    and N1.Inqty >0 and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription

    Union All select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT

    From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0

    and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1

    where a.ItemCode=I1.ItemCode

    Group By a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

    REGARDS

    MANGESH PAGDAHRE

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Jitin, Mangesh

      Thanks for your kind response.

      I executed both the queries and they are giving me the detail data.

      I want sum of qty and value for each warehouse.

      except date I do not want user input.

      Thus, if I have 10 warehouses, my stock statement will have just 10 lines with warehouse , sum of qty and sum of value.

      Thanks

      malhaar

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.