tags:

views:

2816

answers:

4

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.

+3  A: 

You can use:

Select
     count(created_date) as counted_leads,
     created_date as count_date
from
     table
group by
     created_date
fbinder
I used this but the created_date is a field with a full datetime so I had to apply this little hack:DATEADD(dd, 0, DATEDIFF(dd, 0, created_date))in the group by part and it all worked like a charm!! :)
The real napster
Also change the count(created_date) to Count(*). Count with a column only counts the non-null values. As such it can be slower than count(*) if the column is nullable.
GilaMonster
+1  A: 
Select count(created_date) total
     , created_dt
  from table
group by created_date
order by created_date desc
northpole
A: 

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)
Dave Swersky
your where clause here will potentially give you a partial days worth of counts on the oldest day
Scott Ivey
A: 

It is most efficient to do your aggregation by integer and then convert back to datetime for presentation.

select cast(daybucket - 1 as datetime) as count_date ,counted_leads from ( select cast(created_date as int) as DayBucket ,count(*) as counted_leads from mytable group by cast(created_date as int) ) as countByDay