Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: Update total based on a match column?

    Hi guys, this may may a simple solution to you but I don't even know if it's possible. Here's scenario...

    We have a system for users to input job related data. I made four custom buttons to run stored procedures so the users can click on the buttons to calculate the total sum on demand. I got the WBS1 (job) level total working for 3 particular sums (see screenshot named UpdateTotal). The script running in the stored procedure basically sums up the total of each corresponding fee from the grid below. They are Compensation (called Fee in SQL), Consultant Fee and Reimb.Allowance.
    Now I want to populate the WBS2 (phase level) Fee total based on the sum of “phase” (see the fourth column from left). See screen shot named “SumCompensationFeeGroupByPhase” screenshot for a better visual. I got the select statement for the query working, see below:

    SELECT SUM(Projects_Fees.CustCompensation) as totalFee
    FROM Projects_Fees, PR
    WHERE PR.WBS1 = Projects_Fees.WBS1
    AND PR.WBS2 = Projects_Fees.WBS2
    AND PR.WBS2 = ' '
    AND Projects_Fees.WBS1 = '11-100'
    AND PR.[Status] = 'A'
    GROUP BY Projects_Fees.CustPhase, Projects_Fees.WBS1, Projects_Fees.WBS2

    And of course I get two numbers in the result set, one for each phase's total fee, but I have no idea how to tell the database to put phase 60 comp. fee sum into the corresponding phase Fee column.
    When I click on the phase level, I wanted pull the total Fee sum grouped by the phase number and update the corresponding total under each phase level. (see screenshot named Phase60)

    Any suggestion is greatly appreciated!

    Attached Thumbnails Attached Thumbnails UpdateTotal.JPG   SumCompensationFeeGroupByPhase.JPG   Phase60.JPG  

  2. #2
    Join Date
    Oct 2011
    Ok, I modified a script by Wim (thank you) and now it's grouping sum on the phase number and updating the total fee of phase 40 on that phase level. However, it's updating the same total fee number for phase 60 fees as well. How can you tell the db which number should be updating which phase?

    Here's the script and I uploaded a screenshot as well.
    update U
    set U.Fee = COALESCE(T.ConsultFeeSum, 0)
    from PR as U
    LEFT OUTER JOIN (select Projects_Fees.WBS1, sum(Projects_Fees.CustCompensation) as ConsultFeeSum
    from Projects_Fees
    Where Projects_Fees.WBS2 = ' '
    GROUP BY Projects_Fees.CustPhase, Projects_Fees.WBS1
    ) as T ON
    U.WBS1 = T.WBS1
    Where U.WBS2 != ' '
    and U.status = 'A'
    Attached Thumbnails Attached Thumbnails HalfWayWorked.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts