Count number of records inserted by month

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)

Count number of records inserted by month

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-11-21 : 11:25:44

Hi there,

I need to get a count of records by their creation date.

I need something like:

Sep 2012: 185
Oct 2012: 230
Nov 2012: 176

How would I do that?

Thanks as always for your help.

Best Regards,

Steve.

Steve

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 11:27:21

SELECT DATEADD(month,DATEDIFF(month,0,yourDate),0),count(*)
FROM yourTable
GROUP BY DATEADD(month,DATEDIFF(month,0,yourDate),0)

Jim

Let the front end do the formatting if possible

Everyday I learn something that somebody else already knew

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-11-21 : 11:43:43

Hi Jim,

Thanks for your quick response.

That works great but would you know how I can show just
"2012-10-01" instead of "2012-10-01 00:00:00.000" the time part makes it look bad.

Also there is an archive table that I need to include in the counts. Is there a way to combine the 2 counts. The 2 Tables are identical.

Thanks you very much for your help I really appreciate it.

Best Regards,

Steve

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 12:07:08

Use convert(varchar(11),DATEADD(month,DATEDIFF(month,0,current_timestamp),0),121)
in both the select and group by. Wherever the data ends up iw where the formatting should be done, though.
You can combine the 2 tables using union

SELECT DATEADD(month,DATEDIFF(month,0,yourDate),0),count(*)
FROM yourTable
GROUP BY DATEADD(month,DATEDIFF(month,0,yourDate),0)

UNION

SELECT DATEADD(month,DATEDIFF(month,0,yourDate),0),count(*)
FROM yourOtherTable
GROUP BY DATEADD(month,DATEDIFF(month,0,yourDate),0)

Jim

Everyday I learn something that somebody else already knew

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-11-22 : 04:19:54

Hi Jim,

The convert worked perfect. Thankyou.

The union is not as I expected. This puts in 2 values for each month.

Is there a way to combine the values so instead of having.

2012-11-01: 140
2012-11-01: 80

I could have
2012-11-01: 220

Thanks for your continued help, I appreciate it.

Best Regards,

Steve

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-22 : 05:40:49

You can do as follows:


SELECT Column1, SUM(column2)
FROM
   (SELECT DATEADD(month,DATEDIFF(month,0,yourDate),0) Column1 ,count(*)  Column2 
    FROM yourTable
    GROUP BY DATEADD(month,DATEDIFF(month,0,yourDate),0)

    UNION

    SELECT DATEADD(month,DATEDIFF(month,0,yourDate),0),count(*)
    FROM yourOtherTable
    GROUP BY DATEADD(month,DATEDIFF(month,0,yourDate),0)
    ) t
GROUP BY Column1