15,849,829 members
See more: , +
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
------------------------------------------

Posted
Updated 29-Jul-13 4:54am
v2

## Solution 1

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

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

## Solution 2

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

MichealRay 30-Jul-13 6:03am
Good job...
Maciej Los 30-Jul-13 6:30am
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.

## Solution 3

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

MichealRay 30-Jul-13 6:05am
Thank u
you are most welcome..

## Solution 4

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