SQL Count for each date

问题:

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.

回答1:

You can use:

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

回答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)

回答3:

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

回答4:

When you cast a DateTime to an int it "truncates" at noon, you might want to strip the day out like so

cast(DATEADD(DAY, DATEDIFF(DAY, 0, created_date), 0) as int) as DayBucket

回答5:

Select count(created_date) total
     , created_dt
  from table
group by created_date
order by created_date desc

回答6:

CREATE PROCEDURE [dbo].[sp_Myforeach_Date]
    -- Add the parameters for the stored procedure here
    @SatrtDate as DateTime,
    @EndDate as dateTime,
    @DatePart as varchar(2),
    @OutPutFormat as int 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    Declare @DateList Table
    (Date varchar(50))

    WHILE @SatrtDate<= @EndDate
    BEGIN
    INSERT @DateList (Date) values(Convert(varchar,@SatrtDate,@OutPutFormat))
    IF Upper(@DatePart)='DD'
    SET @SatrtDate= DateAdd(dd,1,@SatrtDate)
    IF Upper(@DatePart)='MM'
    SET @SatrtDate= DateAdd(mm,1,@SatrtDate)
    IF Upper(@DatePart)='YY'
    SET @SatrtDate= DateAdd(yy,1,@SatrtDate)
    END 
    SELECT * FROM @DateList
END

Just put this Code and call the SP in This way

exec sp_Myforeach_Date @SatrtDate='03 Jan 2010',@EndDate='03 Mar 2010',@DatePart='dd',@OutPutFormat=106

Thanks Suvabrata Roy ICRA Online Ltd. Kolkata

回答7:

I had similar question however mine involved a column Convert(date,mydatetime). I had to alter the best answer as follows:

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