multiple query in one - help please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

multiple query in one - help please

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-11-17 : 05:08:03

I have the following query to show profit and loss data for the year in my application for expenses


select sum(expenses.amount) as tot, month(expenses.expense_date) as month
from expense_categories inner join expenses on expense_categories.id = expenses.main_catid
where expenses.expense_date between '2009/01/01' and '2009/12/31'
group by month(expenses.expense_date)

But.... here is my problem. I have another date field in the expenses table called paid_date. If the paid_date field is a date and not NULL (it can sometimes be null), I want the query to be this instead


select sum(expenses.amount) as tot, month(expenses.paid_date) as month
from expense_categories inner join expenses on expense_categories.id = expenses.main_catid
where expenses.paid_date between '2009/01/01' and '2009/12/31'
group by month(expenses.paid_date)

This query brings 100's of results back but I can't get it to work incorporating both of them.

Hope that makes sense?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 05:35:39

select sum(expenses.amount) as tot, month(expenses.expense_date) as month
from expense_categories inner join expenses on expense_categories.id = expenses.main_catid
where expenses.expense_date between '2009/01/01' and '2009/12/31' and paid_date is null
group by month(expenses.expense_date)
union all
select sum(expenses.amount) as tot, month(expenses.paid_date) as month
from expense_categories inner join expenses on expense_categories.id = expenses.main_catid
where expenses.paid_date between '2009/01/01' and '2009/12/31'
group by month(expenses.paid_date)

Madhivanan

Failing to plan is Planning to fail

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-11-17 : 05:46:10

Ah, thanks for that I shall try it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources