| Author |
 Topic  |
|
|
Steve2106
Posting Yak Master
United Kingdom
149 Posts |
 Posted - 11/21/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/21/2012 : 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
United Kingdom
149 Posts |
Posted - 11/21/2012 : 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 |
Edited by - Steve2106 on 11/21/2012 11:44:36 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/21/2012 : 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
United Kingdom
149 Posts |
Posted - 11/22/2012 : 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
Flowing Fount of Yak Knowledge
India
1716 Posts |
Posted - 11/22/2012 : 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
--
Chandu |
|
|
|
Steve2106
Posting Yak Master
United Kingdom
149 Posts |
Posted - 11/22/2012 : 09:32:40
|
Hi Chandu,
That worked perfect, just what I needed.
I appreciate your time.
Best Regards,
Steve |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1716 Posts |
Posted - 11/23/2012 : 01:09:42
|
quote:
Originally posted by Steve2106
Hi Chandu,
That worked perfect, just what I needed.
I appreciate your time.
Best Regards,
Steve
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
Count number of records inserted by month
