Grouping data by period

Navigation
0

I am doing a query in the bank, to return the product entry by hour (from 00:00 to 23:00) during a certain day; follows the query: 

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 that I have registered; follows 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

Is it possible to return an amount 0 at a time when it does not have any records?

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

Thank you in advance

    
asked by anonymous 30.03.2017 / 17:54

1 answer

0

Evaluate the following suggestion, which counts production by 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

In the first step of the code the reporting period is informed; you should only inform the days, without schedules.

The second step is to create a complete list of schedules, covering the issuance period. This occurs in the CTEHomeDia.

The third step is to count the production for each day / hour. This is done in the CTEProteD. Note that the most efficient (theoretically) way of converting the time hh: mm: ss to hh: 00: 00 was used.

Finally, the complete list of schedules is issued, adding production counting to day / hour, if any.

José Diz Belo Horizonte, MG - Brazil     
31.03.2017 / 00:51
jQuery.ajax calls webmethod (c #) with SpeechSynthesizer Doubt with algorithm and vector