Date ranges from Jan to Dec - help needed

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)

Date ranges from Jan to Dec - help needed

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-26 : 09:16:21

I need some ideas on the best way to do this:

I am trying to work out profit and loss results from Jan to Dec for a given year.

I have 5 income categories (1 to 5), and my income table is as follows:

income_id
date_received
invoiceid (foreign key linking to an invoice table)
amount
on_bank_statement

At the moment I am looping through on the web-page with sql like

select sum(income.amount) as tot
from invoices inner join income on invoices.id = income.invoiceid
where income.date_received between '2009/01/01' and '2009/01/31' and invoices.type=1

That would obviously calculate the total income in Jan 2009 for the income category 1 (type=1)

I am looping through the page opening and closing record sets 5 times per month, so thats 5 x 12 = 60 times!

There must be a better way of doing this?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 09:25:02

[code]select
sum(income.amount) as tot,invoices.type,month(income.date_received)
from
invoices inner join income on invoices.id = income.invoiceid
where
income.date_received between '20090101' and '20091231' and invoices.type in (1,2,3,4,5)
group by
invoices.type,month(income.date_received)[/code]

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-26 : 09:35:47

You are the man! (or woman)... Many thanks

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-26 : 10:01:55

Although that query does the job brilliantly, how can I expand on it to include the column sum totals?

ie the sum of everyting in Jan, sum of everything in feb etc so that I can have a "Totals" column in the table at the bottom.

Does that make sense?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 10:48:58

try this,

select 	
	sum(income.amount) as tot,invoices.type,month(income.date_received)
from 
	invoices inner join income on invoices.id = income.invoiceid
where 
	income.date_received between '20090101' and '20091231' and invoices.type in (1,2,3,4,5)
group by
	invoices.type,month(income.date_received)
With Rollup