locked
how to sum a field and distribute the sum between records in another table RRS feed

  • Question

  • I have a table whose records consist of minor inventory. I need to sum the cost of these records and distribute the sum in another table based on the price of the record. for example. say the sum of the inventory is is $1000, and I have 4 jobs in the other table.

             table 2                    table 1
    jobname  amount    InvAmnt           item#  cost
      job1   $100,000     -               1     $250
      job2   $50,000      -               2     $150
      job3   $25,000      -               3     $100
      job4   $25,000      -               4     $500

    so i need to sum everything in table 1 then based on the value of the amount in table 2. therefore, 50% of the sum would go to job1, 25% of the sum would go to job2, and 12.5% of the sum would go to job3 and job4. so the final table should look like this:

             table 2          
      jobname  amount    InvAmnt    
       job1    $100,000   $500
       job2    $50,000    $250
       job3    $25,000    $125
       job4    $25,000    $125

    how do I accomplish this?

    Friday, June 12, 2015 2:27 PM

Answers

  • Hi shatcher94,

    Based on your description, to achieve your requirement, you can reference the below sample.

    CREATE TABLE Table2
    (
    jobName VARCHAR(5),
    amount MONEY,
    InvAmnt MONEY
    )
    ;
    INSERT INTO Table2(jobName,amount)
    VALUES
    ('job1',100000),
    ('job2',50000),
    ('job3',25000),
    ('job4',25000);
    
    CREATE TABLE Table1
    (
    item# INT,
    cost MONEY
    )
    ;
    INSERT INTO Table1
    VALUES
    (1,250),
    (2,150),
    (3,100),
    (4,500);
     
    
    ;WITH Cte AS
    (
    SELECT T2.jobName,T2.amount,T2.InvAmnt,ROW_NUMBER() OVER(ORDER BY T2.amount DESC) RN,
    cat.totalCost
    FROM Table2 T2
    CROSS APPLY
    (
    SELECT SUM(cost) totalCost FROM Table1
    ) cat
    ) 
    SELECT jobName,amount,
    CASE WHEN RN=1 THEN 0.5*totalCost
    	 WHEN RN=2 THEN 0.25* totalCost
    	 ELSE 0.125*totalCost 
    END InvAmnt
    FROM Cte
    
    --to update table2
    --;WITH Cte AS
    --(
    --SELECT T2.jobName,T2.amount,T2.InvAmnt,ROW_NUMBER() OVER(ORDER BY T2.amount DESC) RN,
    --cat.totalCost
    --FROM Table2 T2
    --CROSS APPLY
    --(
    --SELECT SUM(cost) totalCost FROM Table1
    --) cat
    --) 
    --UPDATE Cte SET InvAmnt=
    --CASE WHEN RN=1 THEN 0.5*totalCost
    --	 WHEN RN=2 THEN 0.25* totalCost
    --	 ELSE 0.125*totalCost 
    --END  
     
    DROP TABLE Table1,Table2

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Monday, June 15, 2015 1:51 AM