Solved

Budget and Cost Report

Posted on 2012-03-19
1
289 Views
Last Modified: 2012-03-25
I am currently working on the above report.  The monthly budgets are contained in one table, the costs in another.  If work from the budgets table to costs I can see all costs against a cost code that has a budget, but if costs are incurred against a cost code that doesn't have a budget this will be missed.  I could work the other way from costs to budgets but the report wouldn't show costs codes that had a budget but where no costs had been incurred.

Please can someone advise on the best approach?
0
Comment
Question by:Damozz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37739960
Use an outer join to get all costs with their budgets (or lack thereof)

Select...
from Costs c left join budgets b on c.costcode = b.costcode

go the other way to get all budgets with their costs (or lack thereof)
Select...
from Costs c right join budgets b on c.costcode = b.costcode

Most reports are best developed with one of the above forms.  To see it both ways, probably best to use two reports or two sub-reports.


You can also use full outer join  to get all budgets and all costs at one time, but it gets a little tricky developing a meaningful report when sometimes the budgets are missing and sometimes the costs are missing.

Select...
from Costs c full outer join budgets b on c.costcode = b.costcode
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question