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)
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.
set U.Fee = COALESCE(T.ConsultFeeSum, 0)
from PR as U
LEFT OUTER JOIN (select Projects_Fees.WBS1, sum(Projects_Fees.CustCompensation) as ConsultFeeSum
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'