SQL Join with Aggregates problem

I have four tables something like this:

PROJECTS
------------------
ID        NAME
1        Project 1
2        Project 2
3        Project 3


APPROPRIATIONS
---------------------------
APPR_ID     PROJ_ID    APPR_DATE    APPR_AMT
1                   3               12-1-06           12000
2                   2               12-1-07             7000
3                   2               12-1-08             5000
4                   1               12-1-05           50000
5                   3               12-1-07            8000
6                   3               12-1-08            6000

BUDGETS
---------------
BUD_ID     PROJ_ID       BUD_DATE      BUD_AMT
1                  1                12-1-09           5000
2                  3                12-1-09           4000

EXPENDITURES
-------------------
EXP_ID       PROJ_ID       EXP_DATE      EXP_AMT
1                  2                   1-1-08            3000
2                  2                   6-1-08            4000
3                  2                   1-1-09            5000
4                  1                   1-1-06           10000
5                  1                   3-1-06             2000

I need to summarize them to show:
1) Project ID and name
2) Budget date and amount
3) Total appropriations prior to budget date
4) Total expenditures prior to budget date

When I LEFT JOIN and SUM the tables and amounts, the fact that some projects have multiple related rows in one table but only one related row in another causes the SUMs to be off, since some values are duplicated.

How do I make this work correctly?
LVL 1
tchrisAsked:
Who is Participating?
 
reb73Commented:
Here you go -
SELECT PR.[ID], PR.[NAME], BU.BUD_DATE, BU.BUD_AMT, 
       TotalAppropriations = (SELECT SUM(A.APPR_AMT) FROM APPROPRIATIONS A WHERE A.PROJ_ID = PR.PROJ_ID AND A.APPR_DATE < BU.BUD_DATE),
       TotalExpenses= (SELECT SUM(E.EXP_AMT) FROM EXPENDITURES E WHERE E.PROJ_ID = PR.PROJ_ID AND E.EXP_DATE < BU.BUD_DATE)
FROM PROJECTS PR
INNER JOIN BUDGETS BU ON BU.PROJ_ID = PR.PROJ_ID

Open in new window

0
 
mikainzCommented:
This should also work,


select p.id, p.name, b.bud_date, b.bud_amt,
            a.sum_app, e.sum_exp
from projects p left join budgets b on p.id = b.proj_id
       left join
(SELECT proj_id, sum(appr_amt) as sum_app
FROM APPROPRIATIONS
 group by proj_id) a on p.id=a.proj_id
       left join
(SELECT proj_id, sum(exp_amt) as sum_exp
FROM EXPENDITURES
 group by proj_id) e on p.id=e.proj_id
0
 
reb73Commented:
mikainz, your query wont be accurate as the Total Appropriations and Expenditures have to be 'prior to budget date'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mikainzCommented:
sorry I missed that
 
0
 
mikainzCommented:
try this
select p.id, p.name, b.bud_date, b.bud_amt,
           a.sum_app, e.sum_exp
from projects p left join budgets b on p.id = b.proj_id
      left join
(SELECT a1.proj_id, sum(a1.appr_amt) as sum_app
FROM APPROPRIATIONS a1 left join budgets b1
where a1.appr_date < coalesce(b1.bud_date,convert(datetime,'21000101'))
group by a1.proj_id) a on p.id=a.proj_id
      left join
(SELECT e1.proj_id, sum(e1.exp_amt) as sum_exp
FROM EXPENDITURES e1 left join budgets b1
where e1.exp_date < coalesce(b1.bud_date,convert(datetime,'21000101'))
group by e1.proj_id) e on p.id=e.proj_id
0
 
tchrisAuthor Commented:
Many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.