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