Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Totals for each month in a date range query Expand / Collapse
Author
Message
Posted Wednesday, December 02, 2009 5:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #827871
Posted Thursday, December 03, 2009 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, December 03, 2009 2:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #828528
Posted Thursday, December 03, 2009 2:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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!


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #828529
Posted Friday, December 04, 2009 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase ««12

Permissions Expand / Collapse