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