Click here to Skip to main content
15,076,991 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
I Have Two Tables INStock and OUTStock. Merging these two Tables using Join and Getting Current Stock..I've done a query like this..

SQL
SELECT DISTINCT
            (INStock.WareHouse) AS WH,INStock.ItemCode, 
            SUM(INStock.Qty) AS INQty,SUM(OUTStock.Qty) AS OUTQty,
            SUM(INStock.Qty) - SUM(OUTStock.Qty) as Stock
FROM        INStock Left JOIN
                     OUTStock ON INStock.ItemCode = OUTStock.ItemCode 
                     group by  INStock.WareHouse,INStock.ItemCode 
                     Having INStock.ItemCode='0001'


But i Could not Get Correct Result Like Below..

INSTOCK Table
-------------------------
WareHouse |  Code |  Qty
-------------------------
   1      |  0001 |   5
   1      |  5555 |   4
   2      |  0001 |   6
   3      |  0001 |   2
   3      |  4444 |   2
-------------------------


OUTSTOCK Table
--------------------------
WareHouse |  Code |  Qty
-------------------------
   1      |  0001 |   3
   1      |  5555 |   2
   2      |  0001 |   3
   2      |  4444 |   1
-------------------------


I want to Look like this,(For Particular Code '0001')
-------------------------------------------
WareHouse |  Code |  INQty | OutQty  | Stock
-------------------------------------------
   1      |  0001 |   5    |   3     |   2
   2      |  0001 |   6    |   3     |   3
   3      |  0001 |   2    |   0     |   2
------------------------------------------


Please Suggest..
Posted
Updated 29-Jul-13 3:54am
v2

Try this:
SQL
DECLARE @INSTOCK TABLE (WareHouse INT, Code VARCHAR(30),  Qty INT)

INSERT INTO @INSTOCK (WareHouse, Code, Qty)
SELECT 1, '0001', 5
UNION ALL SELECT 1, '5555', 4
UNION ALL SELECT 2, '0001', 6
UNION ALL SELECT 3, '0001', 2
UNION ALL SELECT 3, '4444', 2

 
DECLARE @OUTSTOCK TABLE (WareHouse INT, Code VARCHAR(30),  Qty INT)

INSERT INTO @OUTSTOCK (WareHouse, Code, Qty)
SELECT 1, '0001', 3
UNION ALL SELECT 1, '5555', 2
UNION ALL SELECT 2, '0001', 3
UNION ALL SELECT 2, '4444', 1


SELECT t1.WareHouse, t1.Code, t1.Qty AS INQty, COALESCE(t2.Qty,0) AS OUTQty, t1.Qty - COALESCE(t2.Qty,0) AS Stock
FROM @INSTOCK AS t1 LEFT JOIN @OUTSTOCK AS t2 ON t1.WareHouse = t2.WareHouse AND t1.Code =  t2.Code
WHERE t1.Code = '0001'


Result:
1	0001	5	3	2
2	0001	6	3	3
3	0001	2	0	2
   
Comments
Orcun Iyigun 29-Jul-13 10:10am
   
Piece of cake :) My 5.
Maciej Los 29-Jul-13 10:30am
   
Thank you ;)
MichealRay 30-Jul-13 4:57am
   
@maciej Los,Thank u for ur reply..its So good,but my table has more than 1000 Records.how can i Union All look like this..i will use this query in my table..but the result Only Show as last Warehouse column only like this,
3 0001 2 0 2
Maciej Los 30-Jul-13 5:03am
   
Remove WHERE clause ;)
Try this as well

SQL
CREATE TABLE #INStock  (WareHouse NVARCHAR(50), ItemCode  NVARCHAR(50),Qty INT)

CREATE TABLE #OUTStock ( WareHouse NVARCHAR(50),ItemCode  NVARCHAR(50),Qty INT)

INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','0001',5)
INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','5555',4)
INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('2','0001',6)
INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('3','0001',2)
INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('3','0004',2)

INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','0001',3)
INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','5555',2)
INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('2','0001',3)
INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('2','4444',1)

--SELECT * FROM #INSTOCK

SELECT
            (INStock.WareHouse) AS WH,INStock.ItemCode
            ,
           SUM(ISNULL(INStock.Qty,0)) AS INQty,
            SUM(ISNULL(OUTStock.Qty,0)) AS OUTQty           ,
            SUM(ISNULL(INStock.Qty,0)) - SUM(ISNULL(OUTStock.Qty,0)) as Stock
FROM        #INStock INStock  LEFT JOIN
                     #OUTStock OUTStock
                     ON INStock.ItemCode = OUTStock.ItemCode
                     AND INStock.WareHouse = OUTStock.WareHouse
                     group by  INStock.WareHouse,INStock.ItemCode
                     Having INStock.ItemCode='0001'
                     order by INStock.WareHouse



drop table #INStock
drop table #OUTStock
   
Comments
MichealRay 30-Jul-13 6:03am
   
Good job...
Maciej Los 30-Jul-13 6:30am
   
Please, mark this answer as a solution (formally) if it was helpfull ;)
Mukesh Ghosh 30-Jul-13 6:36am
   
It should work.How to mark this as a solution.
Maciej Los 30-Jul-13 7:11am
   
Sorry, Mukesh Ghosh, but my comment was addressed to guyzfree. Only OP can mark answer as solution.
Hi,
try this

SQL
select I.WareHouse,I.Code,ISNULL(I.Qty,0) InQty,ISNULL(O.Qty,0) OutQty , (ISNULL(I.Qty,0)-ISNULL(O.Qty,0)) Stock
from INStock I(nolock)
left join
OUTStock O (nolock)
on I.warehouse=O.warehouse
and I.code=O.Code
   
Comments
MichealRay 30-Jul-13 6:05am
   
Thank u
Adarsh chauhan 30-Jul-13 6:07am
   
you are most welcome..
hi,
i retrieve Correct ans with the Help of @maciej Los and @Mukesh Ghosh..here is My Query..

SQL
Select WH,Code,INQty,OUTQty,Stock from(
SELECT
          (INStock.WareHouse) AS WH,INStock.ItemCode code,
           SUM(ISNULL(INStock.Qty,0)) AS INQty,
            SUM(ISNULL(OUTStock.Qty,0)) AS OUTQty,
            SUM(ISNULL(INStock.Qty,0)) - SUM(ISNULL(OUTStock.Qty,0)) as Stock
FROM        WH_INCOMING INStock  LEFT outer JOIN
                     WH_OUTGOING OUTStock
                     ON INStock.ItemCode = OUTStock.ItemCode
                      and INStock.WareHouse = OUTStock.WareHouse
                     group by  INStock.WareHouse,INStock.ItemCode
                    ) A where code = '008' order by WH
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900