Date Count - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date Count

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-12-12 : 09:56:10

Hi There,
Can you help me please.

I have a table of events and I need to display a count of events for each month of each year. The table layout looks like this:

AutoId
Event
Date

Sample Data:
1, Piano Concert, 01/09/2009
2, Members Gala, 02/09/2009
3, Residents Arobic class, 15/10/2009
4, Races Night, 12/01/2010

I need to create a query count which would show:

year: Month (Count)
2009: Sept (2) | Oct (1)
2010: Jan (1)

Hope that makes sense and someone can help.

Best Regards,

Steve

a.rameshk
Starting Member

19 Posts

Posted - 2009-12-12 : 10:52:41

Hi,
The below query can help

SELECT DATEPART(YY,DATES) as [Year],DATENAME(MM,Dates)as [Month],Count(Event)as [Count]
FROM GetEvent
GROUP BY DATEPART(YY,DATES),DATENAME(MM,Dates)

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-12-14 : 03:50:35

Hi There,

Thanks for your help I appreciate you taking the time.

Best Regards,

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-12-14 : 09:22:17

Hi there,

This worked great but I do have a problem.

It displays in alphabetical month order so April comes before Jan & feb. Is there any way that I can show.
Jan 2009 10
Feb 2009 04
Mar 2009 11
Apr 2009 03

Etc.

Thanks for your help I appreciate it.

Best Regards,

Steve

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 09:24:37

Include

ORDER BY DATENAME(MM,Dates)

in the end of the query

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-12-14 : 10:11:44

Hi There,

I have managed to get the table sorted by the month number using "MONTH(DateRaised)"

The last thing I need now is to have an acumalative total.

Mon | Year |Count | Acc
Jan | 2009 | 10 | 10
Feb | 2009 | 04 | 14
Mar | 2009 | 11 | 25
Apr | 2009 | 03 | 28

I hope that is doable.

Thanks for your help once again.

Best Regards,

Steve

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 10:22:39

Accumulative total of what? its not there in your sample data or your expected output?

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-12-14 : 10:44:19

quote:
Originally posted by vijayisonly

Accumulative total of what? its not there in your sample data or your expected output?

Sorry,
Acumalative total of Count(Event). I have shown it in my post above. (acc)

I have used the query that a.rameshk gave as that worked perfectly (2nd Post).

Thanks for taking the time to help.

Best Regards,

Steve

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 11:48:23

Here's an example on how to do it with your sample data..
Might not be really fast..but it'll work

declare @t table (id int,mon varchar(20), yr int,cnt int)
insert @t
select 1,'Jan', 2009, 10 union all
select 2,'Feb', 2009, 04 union all
select 3,'Mar', 2009, 11 union all
select 4,'Apr', 2009, 03 

select *, (select sum(cnt) from @t t2 where t2.id <= t1.id) as acc
from @t t1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 12:11:34

Here's one from your initial sample data from your first post...

Sample data

declare @t table (Autoid int, [Event] varchar(100), Date datetime)
insert @t
select 1, 'Piano Concert', '09/01/2009' union all
select 2, 'Members Gala', '09/02/2009' union all
select 3, 'Residents Arobic class', '10/15/2009' union all
select 4, 'Races Night', '01/12/2010'

Query

;with Seqtable(id,[Year],[Month],[Count])
AS (
	
	SELECT row_number() over(order by mth) as id,[Year],[Month],[Count]
	from (
		select DATEPART(YY,Date) as [Year],DATENAME(MM,Date)as [Month],datepart(mm,Date) as mth,Count(Event)as [Count]
		FROM @t
		GROUP BY DATEPART(YY,Date),DATENAME(MM,Date),datepart(mm,Date)
		) t
	)

select *, (select sum([Count]) from Seqtable t2 where t2.id <= t1.id) as acc
from Seqtable t1

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-12-15 : 06:16:25

Hi vijayisonly,
Thanks for the replies.

I'll try to incorporate your examples.

I appreciate your help.

Best regards,

Steve

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-15 : 09:57:32

Np...You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources