Início / dba / Perguntas / 193030
Accepted
Elio Fernandes
Asked: 2017-12-14 08:26:52 +0800 CST

Contar o total de ocorrências para cada dia em determinadas datas

  • 772

Tenho esta tabela e não tenho SQL suficiente para fazer uma consulta para obter o resultado mostrado na imagem abaixo.

CREATE TABLE myDates (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')

Veja todos os dias entre 'startDate' e 'endDate' e conte o total de ocorrências por dia:

Resultado esperado insira a descrição da imagem aqui

Acabei de adicionar esta imagem de um arquivo do Excel para ilustrar melhor o que estou tentando alcançar. Estou migrando do Excel para o SQL. Ignore finais de semana e 22/12 e 25/12.

insira a descrição da imagem aqui

Regrads, Elio Fernandes

count

2 respostas

  1. Aqui está uma abordagem baseada em conjunto que usa uma expressão de tabela comum (CTE) para gerar uma tabela de dimensão de data rápida e, em seguida, usamos a CTE para agrupar por data

    A resposta é a mesma que @nates

    Declare @myDates TABLE  (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into @myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')
;

WITH dates ([Date])
AS (
    SELECT convert(DATE, '2017-01-01') AS [Date] -- Put the start date here

    UNION ALL

    SELECT dateadd(day, 1, [Date])
    FROM dates
    WHERE [Date] <= '2020-12-31' -- Put the end date here 
    )
SELECT [Date]
    ,count(*)
FROM dates d
JOIN @myDates m ON d.[Date] >= m.startDate
    AND d.[Date] <= m.endDate
GROUP BY [Date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!
    • 4
  2. Best Answer

    Esta é uma abordagem bastante "procedural", em oposição ao RDBMS baseado em conjunto/puro, mas deve funcionar. Basicamente, você cria uma "tabela de frequência" que rastreia quantas vezes sua tabela de origem tem uma linha que contém "uma data", para cada data de minaté max. Código de exemplo criado para seguir o seu (ou seja, você já criou sua myDatestabela e a preencheu com dados).

    CREATE TABLE #DateFrequency (theDate date, freq int);
--TRUNCATE TABLE #DateFrequency;

DECLARE @theDate date, @maxDate date;
SELECT @theDate = MIN(startDate), @maxDate = MAX(endDate)
    FROM myDates

WHILE (@theDate <= @maxDate)
BEGIN
    INSERT INTO #DateFrequency (theDate, freq)
    SELECT @theDate, COUNT(id)
    FROM myDates
    WHERE @theDate BETWEEN startDate AND endDate;

    SET @theDate = DATEADD(DAY, 1, @theDate);
END

SELECT * FROM #DateFrequency;
--DROP TABLE #DateFrequency;

    Em seguida, faça o que for necessário com os dados em #DateFrequency; torne-a uma tabela permanente (em vez de #temporary) se precisar.

    PS: leia esta postagem no blog de Aaron Bertrand sobre como lidar com intervalos de datas; não é direcionado diretamente ao seu problema porque você (corretamente) usou o Datetipo em vez de DateTime, mas ainda é o que considero leitura obrigatória se você estiver lidando com esse tipo de dados e esses tipos de consultas.

    • 1

relate perguntas