Selecting data and organizing by 15min Sql interval

Question

Good afternoon, I have the following query that returns the amount of vehicles on the track between a certain time. But I want to take this interval between times and set intervals between 15min. Can someone help me ?

@CodLocal int = 38,
@Data varchar(10) = '2019-05-16',
@HoraIni varchar(8) = '08:00:00',
@HoraFin varchar(8) = '09:00:00'
as


Select  M.* , "TotalEq"  = CASE NomTipo
    WHEN 'Motocicletas/Bicicletas'  THEN (Total*0.35)
    WHEN 'Automóveis'               THEN (Total*1)
    WHEN 'Caminhão/Ônibus'          THEN (Total*2)
    WHEN 'Caminhões 3 ou 4 Eixos'   THEN (Total*3)
    WHEN'Caminhões 5 ou mais Eixos' THEN (Total*4)
    END

 from
(


Select "HoraIni"  = @HoraIni, "HoraFin" = @HoraFin,  Data,  
"NomTipo" = case Tipo 
    WHEN 1 THEN 'Motocicletas/Bicicletas'
    WHEN 2 THEN 'Automóveis' 
    WHEN 3 THEN 'Caminhão/Ônibus'
    WHEN 4 THEN 'Caminhões 3 ou 4 Eixos'
    WHEN 5 THEN 'Caminhões 5 ou mais Eixos'

End, 
Lc.NomLocal, "Total" = count(*)   from TbColeta Cl
Join TbLocal Lc on Cl.CodLocal = Lc.CodLocal

where Cl.CodLocal = @CodLocal and Data = @Data and Hora Between @HoraIni and @HoraFin 
group by Data, Tipo, Lc.NomLocal
) M

Answer 1

You can create a CTE with the possible intervals within the times you have determined and so cross the data you already have. For the ranges use the clause WITH as follows:

DECLARE @inicio DATETIME = '2019-05-16 08:00:00';
DECLARE @fim DATETIME = '2019-05-16 09:00:00';

WITH intervalos AS (
  SELECT 1 AS id,
         @inicio AS inicio,
         DATEADD(MINUTE, 15, @inicio) AS fim
  UNION ALL
  SELECT i.id + 1 AS id,
         i.fim AS inicio,
         DATEADD(MINUTE, 15, i.fim) AS fim
    FROM intervalos i
   WHERE i.fim < @fim
)
SELECT *
  FROM intervalos i

Which will result in the following:

id  | inicio                  | fim
---------------------------------------------------
1   | 2019-05-16 08:00:00.000 | 2019-05-16 08:15:00.000
2   | 2019-05-16 08:15:00.000 | 2019-05-16 08:30:00.000
3   | 2019-05-16 08:30:00.000 | 2019-05-16 08:45:00.000
4   | 2019-05-16 08:45:00.000 | 2019-05-16 09:00:00.000

With this table you only need to perform one JOINand will have the desired result.

---

WITH

APPLIES TO: SQL Server (from 2008 onwards), Banco de Dados SQL do Azure, SQL Data Warehouse do Azure and Parallel Data Warehouse

Specifies a temporary named result set, known as a CTE (common table expression). It is derived from a simple query and defined in the execution scope of a single instruction SELECT, INSERT, UPDATE, DELETE or MERGE. This clause can also be used in a CREATE VIEW statement as part of the instruction SELECT that defines it. A common table expression can include references to itself. This is what we call the common recursive table expression.

Syntax

[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
expression_name [ ( column_name [ ,...n ] ) ]  
AS  
( CTE_query_definition )

Arguments

expression_name

Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same clause WITH <common_table_expression>, but expression_name may be the same as the name of a base table or display. Any reference to expression_name query uses the common table expression, not the base object.

column_name

Specifies a column name in the common table expression. Duplicate names are not allowed in a single definition of CTE. The number of column names specified shall correspond to the number of columns in the result set of the CTE_query_definition. The list of column names will be optional only if separate names are provided for all the resulting columns in the query definition.

CTE_query_definition

Specifies an instruction SELECT whose result set populates the common table expression. The statement SELECT of CTE_query_definition must meet the same requirements as for the creation of an exhibition, with the exception that a CTE cannot define another CTE. For more information, see the Comments and CREATE VIEW (Transact-SQL).

If more than one CTE_query_definition is defined, the definitions of consultation may be joined by one of these two sets of operators: UNION ALL, UNION, EXCEPT or INTERSECT.

Answer 2

Failed to inform how the columns are declared.

-- código #1
@CodLocal int = 38,
@Data varchar(10) = '2019-05-16',
@HoraIni varchar(8) = '08:00:00',
@HoraFin varchar(8) = '09:00:00'
as

with 
Selecionado as (
SELECT Tipo, 
       (datediff (minute, 0, HORA) / 60) as H,
       --(datediff (minute, 0, HORA) % 60) as M,
       ((datediff (minute, 0, HORA) % 60) / 15) as G   
  from tbColeta  
  where CodLocal = @CodLocal 
        and Data = @Data 
        and Hora between @HoraIni and @HoraFin 
),
Agrupado as (
SELECT H, G, Tipo, count(*) as Qtd
  from Selecionado
  group by H, G, Tipo
)
SELECT @CodLocal as [Código do local],
       (SELECT top (1) NomLocal from tbLocal where CodLocal = @CodLocal) as [Nome local],
       @Data as Data,
       cast (dateadd (minute, (H * 60) + (G * 15), 0) as time(0)) as Inicio,
       cast (dateadd (second, (H * 3600) + ((G + 1) * 15 * 60 -1), 0) as time(0)) as Fim,
       case Tipo 
            when 1 then 'Motocicletas/Bicicletas'
            when 2 then 'Automóveis' 
            when 3 then 'Caminhão/Ônibus'
            when 4 then 'Caminhões 3 ou 4 Eixos'
            when 5 then 'Caminhões 5 ou mais Eixos' end as [Tipo de veículo],
       case Tipo 
            when 1 then Qtd * 0.35
            when 2 then Qtd 
            when 3 then Qtd * 2
            when 4 then Qtd * 3
            when 5 then Qtd * 4 end as TotalEq
  from Agrupado
  order by Inicio, Fim;

In CTE Selecionado, the value of the HOUR column is separated into hours, minutes and minute range (0 to 0 to 14; 1 to 15 to 29 etc).

In CTE Agrupado the calculation is carried out for each lane and type of vehicle.