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

User · Answer

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

User · Answer

You can use    Select      count created date  as counted leads       created date as count date from      table group by      created date

User · Answer

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

User · Answer

Select count created date  total        created dt   from table group by created date order by created date desc

User · Answer

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 lt    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

User · Answer

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

User · Answer

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     lt  10 GROUP BY convert varchar  created date  101

[sql] SQL Count for each date

Examples related to sql

Examples related to tsql