I am trying to put together a simple budget and expense tracking tool with three tables.
Very simply, there is a budget table which includes the name of the budget and the budget amount.
There is a table holding commitments, where each commitment has a number (for referencing), a amount and the name of the budget it is held against.
The third table holds details of actual expenses, with the name of the budget the expense is held against and the expense amount. If the expense is linked to a commitment, then the commitment number can also be used.
In summary, the rules are:
- every commitment must be held against a budget, although not every budget will have commitments,
- every expense must be held against a budget,
- not all expenses are held against a commitment,
- a budget might have expenses but no commitments,
- a commitment might not yet have an expense posted against it, and
- all amounts are summar开发者_如何学Goised.
The result I am trying to produce is a report showing each budget, the total of expenses posted to it and the total of (commitments less expenses posted to the commitment) posted to that budget.
I can (1) link a budgets to expenses or (2) link budgets to commitments. But I can't link all three to produce the right result - I end up with huge sums because every expense is shown against every commitment.
I've looked at lots of the examples here (and will keep looking) but understanding the actual workings of what is presented is a bit beyond me right now.
Instead of someone putting together a fully worked solution (which I don't really expect), can someone provide a simple explanation as to how to get the following results:
- Table 1: Budgets -> every row to appear
- Table 2: Expenses -> summed by budget, where expenses exist for the budge
- Table 3: Remaining commitment -> summed by budget, where a commitments exists AND less expenses where expenses have been posted to the commitment.
I'm happy to post my database structures, but frankly I've been working for this on hours and there's not much to show as yet!
[EDIT] The final result should look like (using the sample structure suggested by mellamokb):
Budget item Budget amount Expenses Remaining commitment
IT 5000 390 770
Admin 1000 250 20
Tech 300 100 0
Development 9000 350 0
Using the following DB structure that I have modelled from your description (I realize this structure is not normalized - I'm trying as best as possible to mimic the description given by OP):
- Budget: ID (PK), Name, Amount
- Commitment: ID (PK), RefNumber, Budget, Amount
- Expense: ID (PK), Budget, Amount, RefNumber (can be NULL)
Here is the sample data I am using for the following examples (in thousands).
Budget:
ID Name Amount
1 IT 5000
2 Admin 1000
3 Tech 300
4 Development 9000
Commitment:
ID RefNumber Budget Amount
1 SYSUPGRADE IT 750.00
2 PHONES Admin 35.00
3 WINDOWS7 IT 300.00
Expense:
ID Budget Amount RefNumber
1 IT 35.00 NULL
2 IT 65.00 NULL
3 IT 125.00 SYSUPGRADE
4 IT 80.00 SYSUPGRADE
5 Tech 25.00 NULL
6 Tech 75.00 NULL
7 Admin 90.00 NULL
8 Development 300.00 NULL
9 Development 50.00 NULL
10 Admin 10.00 PHONES
11 Admin 5.00 PHONES
12 Admin 12.00 NULL
13 Admin 133.00 NULL
14 IT 25.00 WINDOWS7
15 IT 60.00 WINDOWS7
Here are the queries that would satisfy your three Table examples.
Table 1: Budgets -> every row to appear
select
Name, Amount
from
Budget B
Output:
Name Amount
IT 5000.00
Admin 1000.00
Tech 300.00
Development 9000.00
Table 2: Expenses -> summed by budget, where expenses exist for the budge
select
Budget, sum(Amount) As Amount
from
Expense E
group by
Budget
Output:
Budget Amount
Admin 250.00
Development 350.00
IT 390.00
Tech 100.00
Table 3: Remaining commitment -> summed by budget, where a commitments exists AND less expenses where expenses have been posted to the commitment.
select
C.Budget, T.Amount - SUM(E.Amount) as RemainingCommitmentAmount
from
Commitment C
inner join
Expense E on C.RefNumber = E.RefNumber
inner join
(select Budget, SUM(Amount) as Amount from Commitment C group by Budget) T
on T.Budget = C.Budget
group by
C.Budget, T.Amount
Output:
Budget RemainingCommitmentAmount
Admin 20.00
IT 760.00
Edit: Combining them into a single output is as simple as left joining the three results together. Since we want all the budgets to display for sure, we use that as the base query, but since there may or may not be relevant expenses or commitments, we want to join those other results using a left join. Also, we will use the COALESCE
command to replace NULL values with 0. Here is what the query would look like:
select
B.Name as `Budget Item`,
B.Amount as `Budget amount`,
COALESCE(E.Amount, 0) as `Expenses`,
COALESCE(C.RemainingCommitmentAmount, 0) as `Remaining commitment`
from
(
select
Name, Amount
from
Budget B
) B
left join
(
select
Budget, sum(Amount) As Amount
from
Expense E
group by
Budget
) E on B.Name = E.Budget
left join
(
select
C.Budget, T.Amount - SUM(E.Amount) as RemainingCommitmentAmount
from
Commitment C
inner join
Expense E on C.RefNumber = E.RefNumber
inner join
(select Budget, SUM(Amount) as Amount from Commitment C group by Budget) T
on T.Budget = C.Budget
group by
C.Budget, T.Amount
) C on C.Budget = B.Name
Output:
Budget item Budget amount Expenses Remaining commitment
IT 5000.00 390.00 760.00
Admin 1000.00 250.00 20.00
Tech 300.00 100.00 0.00
Development 9000.00 350.00 0.00
Hope this helps!
精彩评论