Calculate maximum averages in SQL Server

Question

enter image description hereI have a table in SQL Server that contains two columns, one is a datetime and the other is the measurement that some sensors get for the datetime.

What I want to achieve is: the maximum of the hourly average per day. (The average per hour is made during a day, and the maximum of those hours is taken).

The maximum of the moving average (8hr) per day. (The average of 00:00am-08:00am, the average of 08:00am-04:00pm and the average of 04:00pm-00:00am, and the maximum of those three averages is taken).

This is the code I have so far, I think I calculate the moving average every 8 hours, but I don't know how to calculate the maximum of the averages.

Can someone help me? Thanks in advance

ALTER PROCEDURE [dbo].[usp_specsO3_select]
    @Date1 date,
    @Date2 date
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        snt.Time,
        CONVERT(DATE, snt.Time) AS DiaMesAnyo,
        sntu.Descripcion,
        DATEDIFF(HOUR, CAST(snt.Time AS DATE), snt.Time) / 8 AS DivideDia,
        AVG(snt.Medida) AS Media
    FROM 
        [SDCTUEst].[dbo].[SensoresNoTrafico] AS snt
    INNER JOIN 
        [SDCTUEst].[dbo].[SensoresNoTraficoUnidades] AS sntu ON snt.ID = sntu.ID
    INNER JOIN 
        [dbo].[TipoSensoresAmbientales] AS tsa ON sntu.TipoEntradaSalida = tsa.Id
    WHERE 
        (snt.Time >= @Date1 AND snt.Time <= @Date2)
    GROUP BY 
        DATEDIFF(HOUR, CAST(snt.Time AS DATE), snt.Time) / 8, snt.Time, snt.ID, sntu.Descripcion
END 

Answer 1

I understood that you want to calculate the maximum value of AVG(snt.Medida). If so you can use order by. Order by mostly working after all commands. Order by Media DESC command firstly will be calculated AVG, after then will sort descending. The first record of the resulting data is the maximum value of the Media field. If you need only maximum value then use the TOP 1 on select for best performance. For example:

ALTER PROCEDURE [dbo].[usp_specsO3_select]
    @Date1 date,
    @Date2 date
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP 1  
        snt.Time,
        CONVERT(DATE, snt.Time) AS DiaMesAnyo,
        sntu.Descripcion,
        DATEDIFF(HOUR, CAST(snt.Time AS DATE), snt.Time) / 8 AS DivideDia,
        AVG(snt.Medida) AS Media
    FROM 
        [SDCTUEst].[dbo].[SensoresNoTrafico] AS snt
    INNER JOIN 
        [SDCTUEst].[dbo].[SensoresNoTraficoUnidades] AS sntu ON snt.ID = sntu.ID
    INNER JOIN 
        [dbo].[TipoSensoresAmbientales] AS tsa ON sntu.TipoEntradaSalida = tsa.Id
    WHERE 
        (snt.Time >= @Date1 AND snt.Time <= @Date2)
    GROUP BY 
        DATEDIFF(HOUR, CAST(snt.Time AS DATE), snt.Time) / 8, snt.Time, snt.ID, sntu.Descripcion
    ORDER BY Media DESC 
END