sqlsql-servermultiple-select

Multiple selects on table to return MAX and time


I have a SQL server table 'Water' that stores water used each hour by individual meters. I am attempting to SUM all of that usage to determine the MAX usage hour for each day and what time it occurred. Where I am running into an issue is figuring out how to return the 'time_local' for each MAX record.

My initial query SUM's all the usage for each hour of each day.

SELECT SUM(consumption) AS Gallons,
time_local,
date_local
FROM water
WHERE consumption_period = 'HOUR' AND date_local > DATEADD(day,-2, GETDATE())
GROUP BY 
time_local,
date_local

This returns SUM'd consumption for each hour of the days.

gallons time_local date_local
275,009 2023-12-19T15:00:00 2023-12-19
184,074 2023-12-19T06:00:00 2023-12-19
392,489 2023-12-18T12:00:00 2023-12-18

I am then getting the MAX for each day using the initial statement as a subquery

SELECT * 
FROM 
(SELECT 
MAX(gallons) AS MaxHour,
date_local 
FROM 
(SELECT SUM(consumption) AS Gallons,
time_local,
date_local
FROM water
WHERE consumption_period = 'HOUR' AND date_local > DATEADD(day,-2, GETDATE())
GROUP BY 
time_local,
date_local) AS t1
GROUP BY date_local) AS t2

This gives me the MAX gallons for each day.

gallons date_local
392,489 2023-12-18
315,744 2023-12-19

What I am having trouble solving is pulling this all together so I can report the 'time_local' that each MAX value occured in to get a result like this.

gallons time_local date_local
392,489 2023-12-18T12:00:00 2023-12-18
315,744 2023-12-19T18:00:00 2023-12-19

I've tried joining t1.gallons = t2.maxhour, but I'm doing something wrong because the system tells me 't1 does not exist'.


Solution

  • you can use A window function for that

    WITH CTE AS (
    SELECT SUM(consumption) AS Gallons,
    time_local,
    date_local
    , ROW_NUMBER() OVER (PARTITION BY date_local ORDER BY  SUM(consumption) DESC) rn
    FROM water
    WHERE consumption_period = 'HOUR' AND date_local > DATEADD(day,-2, GETDATE())
    GROUP BY 
    time_local,
    date_local)
    SELECT
        Gallons
        ,time_local
        ,date_local
    FROM CTE 
    WHERE rn = 1