Totals for each month in a date range query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Totals for each month in a date range query

15 posts, Page 2 of 2

««1 2

Totals for each month in a date range query

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Wednesday, December 02, 2009 5:54 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 32,931, Visits: 26,820

stewsterl (12/2/2009)

Awesome! thanks.. Going to read the article tonight.

Perfect. I love it when folks strive...

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #827871

stewsterl

Posted Thursday, December 03, 2009 8:29 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:18 PM
Points: 20, Visits: 138

Any luck with the query? I read the page of the link you sent. Not sure how to implement this in my project.
SUCKS being a noooob at SQL. But I have leaned a ton from this site thus far.

This is what I came up with:

@CustomerID varchar(15),
@Year varchar(15)

AS
BEGIN
select

sum(case when invoice_date between '1/1/'+@Year and '1/31/'+@Year then total_amount else 0 end) as 'Jan',
sum(case when invoice_date between '2/1/'+@Year and '2/28/'+@Year then total_amount else 0 end) as 'Feb',
sum(case when invoice_date between '3/1/'+@Year and '3/31/'+@Year then total_amount else 0 end) as 'Mar',
sum(case when invoice_date between '4/1/'+@Year and '4/30/'+@Year then total_amount else 0 end) as 'Apr',
sum(case when invoice_date between '5/1/'+@Year and '5/31/'+@Year then total_amount else 0 end) as 'May',
sum(case when invoice_date between '6/1/'+@Year and '6/30/'+@Year then total_amount else 0 end) as 'Jun',
sum(case when invoice_date between '7/1/'+@Year and '7/31/'+@Year then total_amount else 0 end) as 'Jul',
sum(case when invoice_date between '8/1/'+@Year and '8/31/'+@Year then total_amount else 0 end) as 'Aug',
sum(case when invoice_date between '9/1/'+@Year and '9/30/'+@Year then total_amount else 0 end) as 'Sep',
sum(case when invoice_date between '10/1/'+@Year and '10/31/'+@Year then total_amount else 0 end) as 'Oct',
sum(case when invoice_date between '11/1/'+@Year and '11/30/'+@Year then total_amount else 0 end) as 'Nov',
sum(case when invoice_date between '12/1/'+@Year and '12/31/'+@Year then total_amount else 0 end) as 'Dec',
sum(case when year(invoice_date) = 2009 then total_amount else 0 end) as 'Total'

from receivable

WHERE (RECEIVABLE.CUSTOMER_ID=@CustomerID)

END

This works fine if I send a year and a customerid. But the requirement is for a date range to be sent like I had in my other query.
like feb 3 2007 - dec 3 2009

Any help would be awesome.. thanks for the link which helped me get to the above query... Crawl-Walk-Run

Post #828229

Jeff Moden

Posted Thursday, December 03, 2009 2:55 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 32,931, Visits: 26,820

You did real good especially for a first timer on such a thing! You've gotten yourself into just a little bit of a problem because you used BETWEEN instead of the more effective classic Invoice_Date >= start of this month and Invoice_Date < start of next month, but we'll hammer that out. I'm still at work. I'll wittle on this tonight.

--Jeff Moden

Post #828528

Jeff Moden

Posted Thursday, December 03, 2009 2:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 32,931, Visits: 26,820

By the way... here's the next step in turning you from a newbie to an SQL monster... it's what I'll use tonight to solve your problem... heh... unless you beat me to it...

http://www.sqlservercentral.com/articles/Crosstab/65048/

... the author of that article is a pretty good guy, too! Tongue

--Jeff Moden

Post #828529

keith.gerritsen

Posted Friday, December 04, 2009 10:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 9:51 AM
Points: 48, Visits: 366

you could take what theSQLGuru wrote and make it generalizable for all years with Datepart functions.

select sum(case when datepart(mm,Invoice_date)=1 then Total_Amount else 0 end ) as Jan,
 sum(case when datepart(mm,Invoice_date)=2 then Total_Amount else 0 end ) as Feb,
 -- etc
 Datepart(yy, Invoice_date) as Yr
 from #RECEIVABLE
group by  Datepart(yy, Invoice_date)

Post #829072

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions