Forum Discussion
Dynamically Generate a Table from Information Distributed Across Data Structure
TonySwad,
Could you please share sample data of your tables?
Regards,
Lydia
The following are samples of the data in the tables relevant to my posted problem.
The following table relates to a contract entity and is used to relate a schedule to each contract.
Ctr_Id Co_Id Cust_Id Sched_Id
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 |
| 4 | 1 | 4 | 4 |
| 5 | 2 | 5 | 1 |
| 6 | 3 | 1 | 2 |
| 7 | 1 | 2 | 3 |
| 8 | 2 | 3 | 4 |
| 9 | 3 | 4 | 1 |
| 10 | 1 | 5 | 2 |
| 11 | 2 | 1 | 3 |
| 12 | 3 | 2 | 4 |
| 13 | 1 | 3 | 1 |
| 14 | 2 | 4 | 2 |
| 15 | 3 | 5 | 3 |
| 16 | 1 | 1 | 4 |
| 17 | 2 | 2 | 1 |
| 18 | 3 | 3 | 2 |
| 19 | 1 | 4 | 3 |
| 20 | 2 | 5 | 4 |
| 21 | 3 | 1 | 1 |
The following table is the detail for the rate schedules. Each rate schedule is the weighted average of one or more indices. The table contains a row for each index used in a given rate schedule and the weighting applied to the index in that given rate schedule.
Sched_Id Index_Id Index_Proportion
| 1 | 1 | 0.25 |
| 1 | 2 | 0.25 |
| 1 | 3 | 0.25 |
| 1 | 4 | 0.25 |
| 2 | 1 | 0.5 |
| 2 | 2 | 0.5 |
| 3 | 1 | 0.333333333 |
| 3 | 2 | 0.333333333 |
| 3 | 3 | 0.333333333 |
| 4 | 1 | 1 |
This table contains metered volumes assigned to a contract in a given month.
Ctr_Id Mtr_Id Volume Prod Date
| 1 | 9 | 5,810 | 12/1/2017 |
| 1 | 19 | 3,081 | 12/1/2017 |
| 1 | 20 | 2,388 | 12/1/2017 |
| 2 | 2 | 2,985 | 12/1/2017 |
| 2 | 34 | 488 | 12/1/2017 |
| 2 | 39 | 8,470 | 12/1/2017 |
| 3 | 11 | 4,946 | 12/1/2017 |
| 4 | 25 | (7,056) | 12/1/2017 |
| 4 | 40 | 5,687 | 12/1/2017 |
| 5 | 4 | 8,325 | 12/1/2017 |
| 6 | 16 | 8,883 | 12/1/2017 |
| 6 | 18 | (9,178) | 12/1/2017 |
| 6 | 24 | (245) | 12/1/2017 |
| 6 | 31 | (6,818) | 12/1/2017 |
| 6 | 37 | (2,811) | 12/1/2017 |
| 7 | 17 | (2,447) | 12/1/2017 |
| 7 | 30 | 2,288 | 12/1/2017 |
| 8 | 27 | 4,516 | 12/1/2017 |
| 9 | 14 | 7,995 | 12/1/2017 |
| 9 | 21 | 4,011 | 12/1/2017 |
| 9 | 29 | 6,184 | 12/1/2017 |
| 10 | 26 | 7,255 | 12/1/2017 |
| 11 | 7 | (7,500) | 12/1/2017 |
| 11 | 33 | (4,271) | 12/1/2017 |
| 12 | 10 | 4,029 | 12/1/2017 |
| 13 | 5 | (1,131) | 12/1/2017 |
| 13 | 28 | (877) | 12/1/2017 |
| 14 | 1 | (258) | 12/1/2017 |
| 14 | 36 | (844) | 12/1/2017 |
| 14 | 38 | (6,577) | 12/1/2017 |
| 15 | 12 | (4,918) | 12/1/2017 |
| 16 | 3 | 3,982 | 12/1/2017 |
| 17 | 15 | 5,153 | 12/1/2017 |
| 17 | 35 | (9,369) | 12/1/2017 |
| 18 | 6 | (1,346) | 12/1/2017 |
| 19 | 8 | (917) | 12/1/2017 |
| 19 | 13 | 9,838 | 12/1/2017 |
| 20 | 22 | (4,111) | 12/1/2017 |
| 20 | 32 | (9,260) | 12/1/2017 |
| 21 | 23 | (1,758) | 12/1/2017 |
The problem is to allocate the metered volumes per contract-meter-month record to the individual indices to which the metered volume is exposed. I was hopeful of creating a table with the following headings:
Ctr_Id Mtr_Id Prod_Mo Sched_Id Index_Id Volume
such that when I added the volume for a given Ctr_Id Mtr_Id Prod_Mo across Index_Id's, the total would equal the volume for the Ctr_Id Mtr_Id Prod_Mo record in the 3rd table above.
By generating such a table, I can write one set of measures on that table that allows me to report at the level of detail I require.