Счетчик SQL для каждой даты

у меня есть следующая потребность

У меня есть таблица регистрации, в которой каждый день регистрируются лиды.

Теперь мне нужно получить отчет о количестве потенциальных клиентов за каждый день за последние 10 дней.

Допустим, таблица выглядит так:

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

И мне нужно подсчитать количество лидов за каждый день, всего 10 дней. Итак, набор результатов должен выглядеть примерно так:

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

... и так далее

Кто-нибудь знает, как это сделать наилучшим образом? Мое текущее решение повторяется с помощью foreach в С#, но я бы очень хотел передать его на сервер sql, а не в sp.


sql tsql
person The real napster    schedule 30.04.2009    source источник

Ответы (6)


arrow_upward
44
arrow_downward

Ты можешь использовать:

Select
     count(created_date) as counted_leads,
     created_date as count_date
from
     table
group by
     created_date
person Fabio Vinicius Binder    schedule 30.04.2009
comment
Я использовал это, но created_date — это поле с полной датой и временем, поэтому мне пришлось применить этот небольшой хак: DATEADD (dd, 0, DATEDIFF (dd, 0, created_date)) в группе по частям, и все это работало как шарм! ! :) - person The real napster; 30.04.2009
comment
Также измените count(created_date) на Count(). Подсчет со столбцом подсчитывает только ненулевые значения. Таким образом, это может быть медленнее, чем count(), если столбец допускает значение NULL. - person GilaMonster; 01.05.2009
comment
Даже если этот вопрос/ответ довольно старый: чтобы получить дату без времени, вы можете использовать DATE(created_date) вместо raw created_date - тогда результаты будут сгруппированы по дате без времени! - person baris1892; 19.09.2016

arrow_upward
9
arrow_downward

Ваше поле created_date имеет значение datetime, поэтому вам нужно удалить время, прежде чем группировка будет работать, если вы хотите идти по дате:

SELECT COUNT(created_date), created_date 
FROM table 
WHERE DATEDIFF(created_date, getdate()) < 10
GROUP BY convert(varchar, created_date, 101)
person Dave Swersky    schedule 30.04.2009
comment
ваше предложение where здесь потенциально даст вам неполное количество дней в самый старый день - person Scott Ivey; 30.04.2009

arrow_upward
3
arrow_downward

Наиболее эффективно выполнять агрегацию по целому числу, а затем преобразовывать обратно в дату и время для представления.

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
person ahains    schedule 30.04.2009
comment
int работает не так хорошо, как varchar. int часто группирует даты вместе - person tree; 06.03.2013
comment
Я также видел проблемы с преобразованием int. У меня было 6 записей 21/12/2013, и версия int вернула число 1. Версия Varchar вернула число 6. - person slolife; 23.12.2013

arrow_upward
2
arrow_downward

У меня был аналогичный вопрос, однако мой касался столбца Convert (date, mydatetime). Мне пришлось изменить лучший ответ следующим образом:

Select
     count(created_date) as counted_leads,
     Convert(date,created_date) as count_date 
from table
group by Convert(date,created_date)
person user3411804    schedule 26.09.2014

arrow_upward
2
arrow_downward

Когда вы переводите DateTime в int, он «усекается» в полдень, вы можете захотеть вырезать день так

cast(DATEADD(DAY, DATEDIFF(DAY, 0, created_date), 0) as int) as DayBucket
person Niclas Lindgren    schedule 05.11.2012

arrow_upward
1
arrow_downward 

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

Просто введите этот код и вызовите SP таким образом

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

Спасибо Сувабрата Рой ICRA Online Ltd. Калькутта

person Suvabrata Roy    schedule 23.12.2010