How do I do this Stored Proc?

[billmack30]

Quick Background- I am trying to write a simple checkbook app to keep track of a joint acct my wife and I have. We have a specific amount of money we put in to this joint account every month for our bills and we want to start keeping track of how we're doing in each bill. For example, we budget $750 for Food and $150 for Gas. If at the end of the month we see that we've only spent $500 on food, then we can either go food shopping or spend that $250 on something else we might need. The sp I want to write is the datasource for a datalist that i want showing how we're doing during the month for each bill we've budgeted for.

CHECKBOOK
CATID Credit Debit
5 1000 NULL
1 500 NULL
1 NULL 200
4 100 NULL
4 NULL 50

BUDGET
CATID BUDGETAMOUNT
1 750
2 1500
3 160
4 150

CATEGORIES
CATID CATEGORYNAME
1 FOOD
2 RENT
3 CAR
4 GAS
5 MISC DEPOSIT
6 MISC WITHDRAWL


Query Results Desired

CatName Budget Credits Debits
FOOD 750 500 200
RENT 1500 0 0
CAR 160 0 0
GAS 150 100 50


PROCEDURE [dbo].[TotalsForCategoriesByMonth]
(
@datemonth as integer = Null,
@dateyear as integer = Null
)
AS

BEGIN

Select c.category, b.budgetamount, ISNULL(SUM(cb.credit),0) as Credits, ISNULL(SUM(cb.debit),0) as Debits

FROM

budget b

inner join

categories as c
on

b.categoryid = c.categoryid

left join

checkbook cb
on
b.categoryid = cb.categoryid


Where datepart(mm,cb.date) = CASE WHEN (@datemonth IS NULL OR RTRIM(@datemonth) = '') THEN datepart(mm,cb.date) ELSE @datemonth END
and datepart(yyyy,cb.date) = CASE WHEN (@dateyear IS NULL OR RTRIM(@dateyear) = '') THEN datepart(yyyy,cb.date) ELSE @dateyear END



Group by c.category, b.budgetamount

End

I wrote the above procedure and I got what I wanted. It wasnt until I added the WHERE clause to deal with a specific month which is pretty much the whole point.

I received these results with the Where clause

CatName Budget Credits Debits
FOOD 750 500 200
GAS 150 100 50

The problem: no results for insurance or rent. I realize that this is because there are no entries in the CHECKBOOK table for these bills and therefore are not shown. I want results for every bill we have set a budget for (aka every bill in the BUDGET table)

I have thought about using a temp table with a join on BUDGET and CATEGORY tables putting in the values:
CatName Budget Credits Debits
FOOD 750 0 0
RENT 1500 0 0
CAR 160 0 0
GAS 150 0 0

and then updating the temp table to reflect any possible values in the CHECKBOOK table.

Is this the only way to do this?

 

[philhege]

Try making your query category-centric, i.e. use the categories table as the first table and LEFT OUTER JOIN the other tables.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.