SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Count number of records inserted by month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Steve2106
Posting Yak Master

United Kingdom
166 Posts

Posted - 11/21/2012 :  11:25:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/21/2012 :  11:27:21  Show Profile  Reply with Quote
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
Go to Top of Page

Steve2106
Posting Yak Master

United Kingdom
166 Posts

Posted - 11/21/2012 :  11:43:43  Show Profile  Reply with Quote
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

Edited by - Steve2106 on 11/21/2012 11:44:36
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/21/2012 :  12:07:08  Show Profile  Reply with Quote
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
Go to Top of Page

Steve2106
Posting Yak Master

United Kingdom
166 Posts

Posted - 11/22/2012 :  04:19:54  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/22/2012 :  05:40:49  Show Profile  Reply with Quote
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




--
Chandu
Go to Top of Page

Steve2106
Posting Yak Master

United Kingdom
166 Posts

Posted - 11/22/2012 :  09:32:40  Show Profile  Reply with Quote
Hi Chandu,

That worked perfect, just what I needed.

I appreciate your time.

Best Regards,


Steve
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/23/2012 :  01:09:42  Show Profile  Reply with Quote
quote:
Originally posted by Steve2106

Hi Chandu,

That worked perfect, just what I needed.

I appreciate your time.

Best Regards,


Steve


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000