Multiple selects on table to return MAX and time

Question

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'.

Answer 1

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