Generating a daily statistic report SQL

Hi,

I Have a table below.

Query

PKEY id int

name varchar(128)

date_add DateTime

What is the SQL statement to get the number of query on each day?

the output should be date and quantity. There should still be an output even if there is no query on that day.

The only way I can think of is by a table-value UDF. (rough design)

function(startdate, enddate)

{

for each day from start to end

insert into result select count(*) from Query where date_add = currentDate

return

}

Is there a more efficient way to do this?

Thanks,

Max

Something like this will do.

Code Snippet

select dateadd(day,datediff(day,0,date_add),0) [day], count(*) [quantity]

from your_table_goes_here

where date_add between @.startdate and @.enddate

group by dateadd(day,datediff(day,0,date_add),0)

If you need to return "0" for unaccount date, you will need a calendar (or number) table to seed the date to be calculated by.