Skip to Content
0
Dec 27, 2011 at 11:32 AM

Query Help

18 Views

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