Grouping data by period

Question

I am making an appointment at the bank, to return the entry of products per hour (from 00:00 until 23:00) during a given day; follow the consultation:

SELECT CONVERT(VARCHAR(13),DATA_ENTRADA,120)+ ':00:00' AS HORA,COUNT(*) AS QTD 
  FROM Tb_Produtos 
  GROUP BY CONVERT(VARCHAR(13),DATA_ENTRADA,120)+ ':00:00' 
  ORDER BY CONVERT(VARCHAR(13),DATA_ENTRADA,120)+ ':00:00';

This query shows me only the quantities in the times I have record; follow the return:

HORA                 /  QTD

2017-01-01 00:00:00  /  4
2017-01-01 06:00:00  /  2
2017-01-01 07:00:00  /  2
2017-01-01 08:00:00  /  3
2017-01-01 10:00:00  /  1
2017-01-01 12:00:00  /  22
2017-01-01 13:00:00  /  24
2017-01-01 14:00:00  /  43
2017-01-01 15:00:00  /  22
2017-01-01 16:00:00  /  27
2017-01-01 17:00:00  /  18
2017-01-01 18:00:00  /  17
2017-01-01 19:00:00  /  23
2017-01-01 20:00:00  /  28
2017-01-01 21:00:00  /  46
2017-01-01 22:00:00  /  18
2017-01-01 23:00:00  /  10

You can return a quantity of 0 at the time when you have no record?

Ex: TIME / QTD

2017-01-01 00:00:00  /  4
2017-01-01 01:00:00  /  0
2017-01-01 02:00:00  /  0
2017-01-01 03:00:00   / 0
2017-01-01 04:00:00   / 0
2017-01-01 05:00:00  /  0
2017-01-01 06:00:00  /  2
2017-01-01 07:00:00  /  2
2017-01-01 08:00:00 /   3
2017-01-01 10:00:00 /   1
2017-01-01 11:00:00  /  0
2017-01-01 12:00:00  /  22
2017-01-01 13:00:00 /   24
2017-01-01 14:00:00  /  43
2017-01-01 15:00:00 /   22
2017-01-01 16:00:00  /  27
2017-01-01 17:00:00  /  18
2017-01-01 18:00:00  /  17
2017-01-01 19:00:00  /  23
2017-01-01 20:00:00 /   28
2017-01-01 21:00:00  /  46
2017-01-01 22:00:00 /   18
2017-01-01 23:00:00 /   10

Thanks in advance

Answer 1

Evaluate the following suggestion, which accounts for production per day/hour:

-- código #1 v2
-- informa o período de emissão (formato dd/mm/aaaa)
declare @dataInicial datetime, @dataFinal datetime;
set @dataInicial= convert(datetime, '1/1/2017', 103);
set @dataFinal= convert(datetime, '1/1/2017', 103);

-- ajusta horário da data final (23:59:59.997)
set @dataFinal= dateadd(ms, -3, dateadd(day, +1, @dataFinal));

--
with 
-- cria tabela completa de horários para o período de emissão
cteHoraDia as (
SELECT @dataInicial as horaBásica
union all
SELECT DateAdd(hour, +1, horaBásica)
  from cteHoraDia
  where horaBásica < dateadd(hour, datediff(hour, 0, @dataFinal), 0)
),
-- contabiliza a produção por dia/hora
cteProdHora as (
SELECT horaProd= dateadd(hour, datediff(hour, 0, DATA_ENTRADA), 0),
       Count(*) as Qtd
  from Tb_Produtos
  where DATA_ENTRADA between @dataInicial and @dataFinal
  group by dateadd(hour, datediff(hour, 0, DATA_ENTRADA), 0)
)
--
SELECT T1.horaBásica as Horário, IsNull(T2.Qtd, 0) as Qtd
  from cteHoraDia as T1
       left join cteProdHora as T2 on T2.horaProd = T1.horaBásica;
go

The first step of the code informs the period of issue of the report; it should inform only the days, without schedules.

The second step is to create a complete list of timetables covering the broadcast period. This occurs in the CTE cteHoraDia.

The third step is to account for the production of each day/hour. This is done in CTE cte cteProdHora. Note that a more efficient (theoretically) way of converting the hour hh:mm:ss to hh:00:00.

Finally, the full list of schedules is issued, adding the production accounting for day/hour, when it exists.

---

      José Diz     Belo Horizonte, MG - Brazil