adding column value to a specific row. please help!!

Question

Hello,

Newbie to SQL..

I have a drop down list and an existing table.

I want to code:

If someone selects part_code '1389' i would to take the qty of part_code '11040' and add it to sum of '1389' then grouping them by cost_ctr and If someone selects part_code '1404' i would to take the qty of part_code '1354' and add it to sum of '1404' then grouping them by cost_ctr

below is the table:

cost_ctr	part_Code	qty	sum1
2000	11040	100	0
2000	1389	50	100
3000	1389	750	275
3000	11040	200	0
2000	1404	500	600
2000	1354	100	0
3000	1404	500	600
3000	1354	100	0

Code so far: (only works with one part code 1389, need to also work with 1404)

Select B.cost_ctr,part_Code,qty,Case when part_code=1389 then A.sum1 Else 0 End as sum1 from dbo.test123 B INNER JOIN (Select SUM(qty)as sum1,Cost_Ctr from dbo.test123 WHERE part_code IN ('1389','11040') GROUP by Cost_ctr) A on A.Cost_Ctr=B.Cost_Ctr

thanks for the help...

Answer 1

Is it there any logic you are having to get the part_code? Seems like you are taking one row behind. Is that what you are doing or you are having different logic?

Answer 2

Check this example. If you have ID column for your table then it is going to easy.

SELECT d.* 
FROM   data d
JOIN   (
          SELECT MIN(id) lowest_id
          FROM   data 
          WHERE  date = '20/8/2010'
       ) dt ON (dt.lowest_id - 1 = d.id)
UNION
SELECT * 
FROM   data 
WHERE  date = '20/8/2010';

CREATE TABLE data (id int, date date, name varchar(10));

INSERT INTO data VALUES (1, '19/8/2010', 'John');
INSERT INTO data VALUES (2, '19/8/2010', 'Mary');
INSERT INTO data VALUES (3, '20/8/2010', 'Peter');
INSERT INTO data VALUES (4, '20/8/2010', 'Bert');
INSERT INTO data VALUES (5, '20/8/2010', 'Ernie');

Result
id          date        name      
----------  ----------  ----------
2           19/8/2010   Mary      
3           20/8/2010   Peter     
4           20/8/2010   Bert      
5           20/8/2010   Ernie 

Answer 3

No the part_code are entered in a separate table. I just need to get the qty of a part_code 11040 and add it to part_code 1389. And part_code 1354 to 1404 and group by cost_ctr. When some one selects 1354 or 1404. Hope that clears it up.

Answer 4

It might be that the cold I'm having makes my head feel like it's embedded in cotton, but I am not understanding much.

How do you that 1389 and 11040 go together? Is there a table that defines this relation?

For this type of questions it is often a good idea to post:

1) CREATE TABLE for you tables, preferably simplified for the sake of the example.
2) INSERT statements with sample data.
3) The desired result given the sample.
4) A short description of the business rules.
5) Which version of SQL Server you are using.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Not sure what you mean. Try

declare @userSelection varchar(max)
set @userSelection = '1389'  -- '1404'

Select B.cost_ctr,part_Code,qty
   ,Case when part_code=@userSelection and @userSelection in ('1389','1404') then A.sum1 Else 0 End as sum1 
from dbo.test123 B 
INNER JOIN (Select SUM(qty)as sum1,Cost_Ctr 
    from dbo.test123 
    WHERE @userSelection in ('1389','1404') and
       part_code IN (@userSelection, 
          case @userSelection when '1389' then 11040' 
                   when '1404'  then '1354'  end))
    GROUP by Cost_ctr) A 
on A.Cost_Ctr=B.Cost_Ctr

---

Serg