SQL Count for each date

Question

I have the following need

I have a logging table which logs som leads generated each day.

Now I need to pull a report over the amount of leads for each day over the last 10 days.

Lets say the table looks like this:

tbl_leads id int, first_name nvarchar(100), last_name nvarchar(100), created_date datetime 

And I need to count the number of leads for each day, 10 days total. SO the result set should look something like this:

counted_leads | count_date 5             | 2009-04-30 7             | 2009-04-29 5             | 2009-04-28 7             | 2009-04-27 

... and so on

Anyone know how to do this the best possible way? My current solution is iterating with a foreach in c# but I would very much like to hand it on the sql server instead in a sp.

Answer 1

You can use:

Select      count(created_date) as counted_leads,      created_date as count_date from      table group by      created_date 

Answer 2

Your created_date field is datetime, so you'll need to strip off the time before the grouping will work if you want to go by date:

SELECT COUNT(created_date), created_date  FROM table  WHERE DATEDIFF(created_date, getdate()) < 10 GROUP BY convert(varchar, created_date, 101)