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

Answer 2

There are many ways to approach this. This keeps pretty close to your original layout. Basically, the gallon usage by hour is fine, then the NOT EXISTS removes any records which are not the largest for a given day (t2.gallons > guah.gallons). This avoids an additional aggregation so you can keep your column list the same.

Note that this will include records where there are ties on the max. For a continuous field like gallons this might be unlikely, but if you have missing data like 0's or a default value it could happen. In that case you'll need to specify some kind of tie-breaker rule.

with gallon_usage_by_hour as (
    select time_local, 
           date_local,
           sum(consumption) as gallons,
      from water
     where consumption_period = 'HOUR' and date_local > dateadd(day,-2, getdate())
     group 
        by time_local,
           date_local
)
select *
  from gallon_usage_by_hour guah
 where not exists
         (
          select 1
            from gallon_usage_by_hour t2 
           where guah.date_local = t2.date_local 
             and t2.gallons > guah.gallons
         )