Requesting help creating ''by month'' summary query

Question

Requesting help creating ''by month'' summary query

Sean G-331808

SSC Rookie

Points: 27
More actions
May 24, 2006 at 3:04 pm

#114257

Hi guys. I'm trying to determine a way to do this in one query.
I have a table with records spanning over a few months. I want to design a query that will calculate how many records (of certain conditions) exist for each month. There is a datetime field (REQ_DATE_COMPLETE) associated with each request. I explain best by example.. so here's a small one:
My table has records such as
1 Req 1 04/05/06
2 Req 2 04/09/06
3 Req 3 04/21/06
4 Req 4 05/04/06
5 Req 5 05/26/06
6 Req 6 07/12/06
I need a query that will return the following information:
MONTH    Number of Requests Completed
Apr-06   3
May-06 2
Jun-06    0
Sep-06    1
Make sense? Am I expecting too much out of a single query? Would it be too convoluted? If anyone wants to take a crack at it or give me any hints I would appreciate it.
Thanks,
Sean
Sean Glover
Web/SQL Server Developer, JR. Data Analyst
President's Choice Financial

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

This should get you started. You will have to add in all of the months and you will need to tweak it to handle the year.

create table monthgroup (RowID int identity(1,1), MonthDate datetime)

insert into monthgroup

select '04/05/06' union all

select '04/09/06' union all

select '04/21/06' union all

select '05/04/06' union all

select '05/26/06' union all

select '07/12/06'

select CASE datepart(m,monthdate)

when 4 then 'April'

when 5 then 'May'

when 7 then 'July'

end as 'Month',

count(*) as 'Number of Requests Completed'

from MonthGroup

group by datepart(m,monthdate)

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

stax68 SSChampion Points: 11711 More actions · Answer 2

Or:

select datename(m,datepart(m,monthdate)) [MONTH] , count(*) [Number of Requests Completed] from MonthGroup group by datename(m,datepart(m,monthdate))

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Marshall Smith SSCrazy Points: 2041 More actions · Answer 3

Note that, in both of the above examples, April 2005 and April 2006 will be counted as the same thing. It's best to also add a DATEPART(y,monthdate) to your GROUP BY and SELECT clauses.

As an additional note, most reporting applications (Access, Crystal, etc.) will do this much more easily. Depending on your needs, that might not help. But, it is nice to remember that you don't have to do all the work in TSQL.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Aw, what the heck... do it all in SQL... pick your poison...

SELECT STUFF(RIGHT(CONVERT(CHAR(11),MonthDate,113),8),4,3,'-') AS [Month],

COUNT(*) AS [Number of Requests Completed]

FROM #MonthGroup

GROUP BY STUFF(RIGHT(CONVERT(CHAR(11),MonthDate,113),8),4,3,'-')

SELECT LEFT(DATENAME(mm,MonthDate),3)+'-'+RIGHT(DATENAME(yy,Monthdate),2) AS [Month],

COUNT(*) AS [Number of Requests Completed]

FROM #MonthGroup

GROUP BY LEFT(DATENAME(mm,MonthDate),3)+'-'+RIGHT(DATENAME(yy,Monthdate),2)

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ajit-297150 Mr or Mrs. 500 Points: 593 More actions · Answer 5

Check This Out:-

select month(REQ_DATE_COMPLETE) Req_Month ,year(REQ_DATE_COMPLETE) Req_Year,count(REQNO)

from REQ_DATE

group by month(REQ_DATE_COMPLETE) ,year(REQ_DATE_COMPLETE)

order by year(REQ_DATE_COMPLETE) , month(REQ_DATE_COMPLETE)

This way you will have more control and more options to represent your data in different ways.

David Burrows SSC Guru Points: 65142 More actions · Answer 6

Requirement is to show missing months as well

DECLARE @from datetime, @diff int

SELECT @from = DATEADD(month,DATEDIFF(month,0,MIN([Date])),0),

@diff = DATEDIFF(month,MIN([Date]),MAX([Date]))

FROM @Table

SELECT STUFF(CAST(d.[Date] as char(11)),4,6,'-'), COUNT(a.[Date])

FROM (SELECT DATEADD(month,n.number,@from) AS [Date]

FROM master.dbo.spt_values n

WHERE n.type = 'P' AND n.number BETWEEN 0 AND @diff) d

LEFT OUTER JOIN @table a ON DATEADD(month,DATEDIFF(month,0,a.[Date]),0) = d.[Date]

GROUP BY d.[Date]

ORDER BY d.[Date]

I used undocumented table spt_values to get numbers to test with but you should create your own Numbers table

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7