March 30, 2009 at 5:25 am
declare @assign table (prod_code char(10),branch_id int,qty decimal(18,2))
declare @sold table (prod_code char(10),branch_id int,qty decimal(18,2))
insert into @assign(prod_code ,branch_id ,qty)
select 'p1',1,20 union
select 'p1',1,30 union
select 'p2',1,30 union
select 'p2',1,40 union
select 'p3',1,6
insert into @sold(prod_code ,branch_id ,qty)
select 'p1',1,5 union
select 'p1',1,15 union
select 'p2',1,10
select p1.prod_code , sum(p1.qty) - sum(p2.qty)
from @assign p1,@sold p2
where p2.branch_id=p1.branch_id and p1.prod_code=p2.prod_code
group by p1.prod_code
March 30, 2009 at 5:30 am
What is your expected result?
Based on the test data you provided the result is 60 for p1 and 50 for p2.
Mathematically correct.
The way your where condition is written you are performing an inner join. Therewith, p3 is left out.
March 30, 2009 at 5:53 am
result should be p1 ->30 but comming as a 60,I am a not looking for the p3 item
March 30, 2009 at 7:29 am
Since you have multiple records in @assign table for each product in a branch, joining it to the @sold table yields duplicate records for the same product and branch and thereby doubling the assign qty.
You should write a query to only include one row per each product per each branch and then do a join on sold table, like the one given below.
SELECTp1.prod_code, SUM( p1.qty ) - SUM( p2.qty ) AS qty
FROM(
SELECTprod_code, branch_id, SUM( qty ) AS qty
FROM@assign
GROUP BY prod_code, branch_id
) p1
INNER JOIN
(
SELECTprod_code, branch_id, SUM( qty ) AS qty
FROM@sold
GROUP BY prod_code, branch_id
) p2 ON p2.branch_id=p1.branch_id and p1.prod_code=p2.prod_code
GROUP BY p1.prod_code
--Ramesh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply