Displaying balance resulting from two different tables

Question

I have 4 tables:

*department(dept_ID, dept_short_name)

*costCentre(cc_ID, dept_ID, costCentre_name)

*monthlyIncome(income_ID, cc_ID, month, amount)

*utilization(util_ID, cc_ID, month, amount)

One department can have many cost centres. So these cost centre will have their income every month and it will be stored in monthlyIncome table by month. So basically monthlyIncome can have many incomes for just one particular cost centre. Meanwhile, utilization table works in same way but value will be stored when income is used.

So what im trying to do is actually to display the balance available for a particular cost centre to the user. So what it have to do is to calculate the sum in monthlyIncome and minus the sum in utilization according to costCentre and display it in dedicated row as per the related costCentre.

Example:

Department       |     Cost Centre     | Balance Income

     HR                            1                        23.00<----Value here is the sum(monthlyIncome) - sum(utilization), dedicated value for row

                                     2                        26.00

                                     3                        45.00

      IT                            4                        34.00

                                     5                        22.00

Currently my sql statement is as below but it is not yielding as i expect;

"SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
FROM department d, costCentre c, monthlyIncome m, utilization u 
WHERE d.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND c.[cc_ID]=u.[cc_ID]"

Hope im clear here, any idea how to make it possible?

Answer 1

;With cteInc As
(Select cc_ID, Sum(amount) As Amount
From monthlyIncome
Group By cc_ID),
cteUtl As
(Select cc_ID, Sum(amount) As Amount
From utilization
Group By cc_ID)
Select d.Dept_Short_name,
  c.CostCentre_name,
  Coalesce(i.Amount,0) - Coalesce(u.Amount,0) As Balance
From department d
Inner Join costCentre c On d.dept_ID = c.dept_ID
Left Join cteInc i On c.cc_ID = i.cc_ID
Left Join cteUtl u On c.cc_ID = u.cc_ID;

Tom

Answer 2

Check this blog post in addition to Tom's answer

Aggregates with multiple tables

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Im trying to imply it into a site where user will just have to choose a department from a dropdownlist. From the selected department, the given result should be displayed.

I have tried @Tom Cooper's suggestion but it is not accepting since its not starting from select, delete, insert, update or procedure?

Answer 4

You can make a slight modification in this solution by using derived tables instead of CTE, e.g.

Select d.Dept_Short_name,
  c.CostCentre_name,
  Coalesce(i.Amount,0) - Coalesce(u.Amount,0) As Balance
From department d
Inner Join costCentre c On d.dept_ID = c.dept_ID
Left Join (Select cc_ID, Sum(amount) As Amount
From monthlyIncome
Group By cc_ID) i On c.cc_ID = i.cc_ID
Left Join 
(Select cc_ID, Sum(amount) As Amount
From utilization
Group By cc_ID) 
u On c.cc_ID = u.cc_ID;

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

I think the structure is fine, but any idea why its giving error on asp.net platfrom(IErrorInfo.GetDescription failed with E_FAIL(0x80004005).) ?

Answer 6

Post the relevant code from ASP.NET and the actual error. The SQL itself is fine and you can test it separately in SSMS first.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

I have tried to test it with ms access. When i run the query, i get this error:

Syntax error (missing operator) in query expression 'd.dept_ID = c.dept_ID
Left Join (Select cc_ID, Sum(amount) As Amount From monthlyIncomeGroup By cc_ID) i On c.cc_ID = i.cc_ID
Left Join (Select cc_ID, Sum(amount) As Amount From utilization Group By cc_ID)
u On c.cc_ID = u.cc_ID'

Answer 8

Are you using Access database? The syntax we provide work in SQL Server (as this is Transact-SQL forum). For Access you may need to do some slight adjustments in the code. You may want to to ask this question in Access specific forum.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog