I have a table of records with the following columns:
Key (PK, FK, int) DT (smalldatetime) Value (real)
The DT is a datetime for every half hour of the day with an associated value
E.g.
Key DT VALUE
1000 2010-01-01 08:00:00 80
1000 2010-01-01 08:30:00 75
1000 2010-01-01 09:00:00 100
I need to find the max value and associated DT for every 24 hour period. for a particular key and date range
Currently I have:
SELECT CAST(LEFT([DT],11) as smalldatetime) as 'DATE'
,max([VALUE]) as 'MAX_HH'
FROM TABLE 1
WHERE DT > '6-nov-2016' and [KEY] = '1000'
GROUP BY CAST(LEFT([DT],11) as smalldatetime)
ORDER BY 'DATE'
But this returns the max values for the date e.g.
Key DT VALUE
1000 2010-01-01 00:00:00 100
Any ideas on how to pull the full DT?
Damien's answer is very good, if you can't (or want) to use windowed function, try this:
SELECT T1.*
FROM TABLE_1 AS T1
INNER JOIN (
SELECT CAST([DT] as date) as 'DATE'
, MAX([VALUE]) as 'MAX_HH'
FROM TABLE_1
WHERE DT > '6-nov-2016' and [KEY] = '1000'
GROUP BY CAST([DT] as date)
) AS MAX_DT
ON MAX_DT.[DATE] = CAST(T1.[DT] as date)
AND T1.VALUE = MAX_DT.MAX_HH
WHERE DT > '6-nov-2016' and [KEY] = '1000'
ORDER BY DT
By the way, it's best not to use reserved keywords as object names (i.e. date)