how to construct sql query............?
hello i am newbie in sql server so any one teach me how to merge this two query in one. actually this two work fine individually.
1)select distinct(a.qty) as 'processed', b.item_ref from stock a
join stock_chk b on a.item_code=b.item_code and a.loc_id='050' and b.loc_id='050'
2)select sum(qty) as unprocessed ,item_ref from stock_chk group by item_ref
More Questions From This User See All
Answers & Comments
Verified answer
DECLARE @Stock TABLE (
Qty INT,
Item_Code INT,
Loc_ID INT
);
DECLARE @Stock_Chk TABLE (
Qty INT,
Item_Code INT,
Loc_ID INT,
Item_Ref INT
);
INSERT INTO @Stock (Qty, Item_Code, Loc_ID) VALUES (102, 1, '050');
INSERT INTO @Stock (Qty, Item_Code, Loc_ID) VALUES (54, 2, '060');
INSERT INTO @Stock (Qty, Item_Code, Loc_ID) VALUES (33, 3, '050');
INSERT INTO @Stock (Qty, Item_Code, Loc_ID) VALUES (76, 4, '050');
INSERT INTO @Stock_Chk (Qty, Item_Code, Loc_ID, Item_Ref) VALUES (200, 1, '050', 1);
INSERT INTO @Stock_Chk (Qty, Item_Code, Loc_ID, Item_Ref) VALUES (100, 2, '060', 2);
INSERT INTO @Stock_Chk (Qty, Item_Code, Loc_ID, Item_Ref) VALUES (50, 3, '050', 3);
INSERT INTO @Stock_Chk (Qty, Item_Code, Loc_ID, Item_Ref) VALUES (100, 4, '050', 4);
SELECT
DISTINCT(a.qty) AS processed,
SUM(b.qty) AS unprocessed,
b.item_ref
FROM
@stock a
INNER JOIN @stock_chk b ON
a.item_code = b.item_code
AND a.loc_id = b.loc_id
WHERE
a.Loc_ID = '050'
GROUP BY
a.Qty,
b.item_ref;