August 14, 2012 at 6:05 am
Hi,
I have 2 tables as
Table1( Date, Product, Qty ). The data in that table is
Date Product Qty
10/10/12 A 10
10/10/12 A 5
11/10/12 A 20
10/10/12 B 15
11/10/12 B 10
And there is another table as Table2(Product,Qty).
The output which i am expecting is
Product Qty
A 35
B 25.
I tried it using update with subquery and group by but i am not able to get the actual output.
August 14, 2012 at 6:11 am
This does the trick.
declare @table1 table (date date, product char(1), qty int)
insert into @table1 values
('2012-10-10','A',10),
('2012-10-10','A',5),
('2012-10-11','A',20),
('2012-10-10','B',15),
('2012-10-11','B',10)
declare @table2 table (product char(1), qty int)
insert into @table2 values
('A',0),
('B',0)
update
@table2
set
qty = dev.qty
from
(
select
Product,
SUM(qty) as qty
from
@table1
group by
product
) as dev
inner join
@table2 t2
on
dev.product = t2.product
select * from @table1
select * from @table2
August 14, 2012 at 6:21 am
Hi
If you have products in Table2 that do not exist in Table1 and you want NULLS to be populated change the join to a LEFT JOIN
CREATE TABLE #TEMP
(Date DATE, Product CHAR(1), Qty INT)
INSERT INTO #TEMP
SELECT '10/10/12', 'A', 10 UNION ALL
SELECT '10/10/12', 'A', 5 UNION ALL
SELECT '11/10/12', 'A', 20 UNION ALL
SELECT '10/10/12', 'B', 15 UNION ALL
SELECT '11/10/12', 'B', 10
CREATE TABLE #TEMP2
(Product CHAR(1), Qty INT)
INSERT INTO #TEMP2
SELECT 'A', 10 UNION ALL
SELECT 'B', 15
SELECT *
FROM #TEMP
SELECT *
FROM #TEMP2
UPDATE #TEMP2
SET Qty = T1.Qty
FROM
#TEMP2 AS T2
INNER JOIN (SELECT SUM(Qty) AS Qty, Product FROM #TEMP GROUP BY Product) AS T1
ON T2.Product = T1.Product
SELECT *
FROM #TEMP2
DROP TABLE #TEMP
DROP TABLE #TEMP2
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
August 14, 2012 at 6:29 am
Another way but using merge
create table table1 (date date, product char(1), qty int)
insert into table1 values
('2012-10-10','A',10),
('2012-10-10','A',5),
('2012-10-11','A',20),
('2012-10-10','B',15),
('2012-10-11','B',10)
create table table2 (product char(1), qty int)
insert into table2 values ('A',0),('B',0),('C',9999999)
GO
merge into
table2 as target
using
(
select
product,
sum(qty) as qty
from
table1
group by
product
) as source
(
product,
qty
)
on
target.product = source.product
when not matched by target
then insert
(product, qty) values (product, qty)
when matched and target.qty <> source.qty
then update
set target.qty = source.qty
when not matched by source
then update
set target.qty = null;
select * from table2
August 14, 2012 at 6:31 am
Antony i tried your first solution it is working but it is showing me only the value of A. It is not showing subsequent rows i.e not showing me B and onwards .
August 14, 2012 at 6:33 am
Which answer?
I have just run the script in the first with @table1 and @table2 and get values for A and B back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply