Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Could any one please help me how to write a script for the below problem.
Please note that "Budget table" is uploaded from excel sheet and "Revenue table" is a selected list from 3 tabels.
Budget table:
BDEPARTNO | BCUSTNO | BAREACODE | BSERVCODE | BAMOUNT |
70 | 25000 | |||
50 | 40000 | |||
70 | 100001 | 5000 | ||
50 | 100004 | 10000 | ||
70 | 100001 | Fyn | 2500 | |
70 | 100001 | Jylland | 2500 | |
70 | 100001 | Jylland | 1052 | 5000 |
70 | Jylland | 20000 | ||
70 | Jylland | 1052 | 10000 |
Revenue table:
DEPARTNO | CUSTNO | AREACODE | SERVCODE | AMOUNT |
50 | 100004 | 500 | ||
50 | 100001 | Fyn | 1052 | 135 |
50 | 100004 | 7011 | 175 | |
50 | 100001 | Fyn | 2531 | 25379 |
50 | 100001 | 2532 | 8459 | |
50 | 100001 | 2532 | 10151 | |
50 | 100001 | 2532 | 2537 | |
70 | 100001 | 7011 | 100 | |
70 | 100001 | Fyn | 7011 | 100 |
70 | 100001 | Jylland | 7011 | 559 |
70 | 100001 | 1052 | 190 | |
70 | 100004 | 190 | ||
70 | 100004 | Jylland | 1052 | 175 |
70 | 100001 | Jylland | 1052 | 545 |
70 | 100840 | 7011 | 335 | |
70 | 100840 | 7011 | 22680 | |
70 | 100001 | 7011 | 485 |
Now I have to add another column of BRAMOUNT in "Budget table". Here BRAMOUNT is a calculated amount from "Revenue table" based on the below conditions.
If any of the field BDEPARTNO/BCUSTNO/BAREACODE/BSERVCODE has been filled with a value in "Budget table", then I have to collect the records from "Revenue Table" and sum up of their AMOUNT's and move it to the new field BRAMOUNT of "Budget table".
If non of the filed BDEPARTNO/BCUSTNO/BAREACODE/BSERVCODE has been selected then I have sum up all the record's AMOUNT and move it to the new field BRAMOUNT of "Budget table".
At last the resulted "Budget table" looks like below:
BDEPARTNO | BCUSTNO | BAREACODE | BSERVCODE | BAMOUNT | BRAMOUNT |
70 | 25000 | 25359 | |||
50 | 40000 | 47336 | |||
70 | 100001 | 5000 | 949 | ||
50 | 100004 | 10000 | 675 | ||
70 | 100001 | Fyn | 2500 | 100 | |
70 | 100001 | Jylland | 2500 | 1104 | |
70 | 100001 | Jylland | 1052 | 5000 | 545 |
70 | Jylland | 20000 | 1279 | ||
70 | Jylland | 1052 | 10000 | 720 |
Thanks in advane.
Use a join between the tables and apply your logic.
I think you can easly solve the problem in the script.
regards Matteo
Hi,
Here you are a possible solution.
I hope it helps you.
Regards.