I am using Microsoft SQL Server 2012.
For example, suppose you have a table with the following records:
The record is one month's worth of data every 10 minutes.(4032 Row)
DateTime, Value1, Value2
2018-02-0100: 10:000.30.3
2018-02-0100:20:000.30.6
2018-02-01 00:30:00 0.51.1
2018-02-0100:40:000.21
2018-02-01 00:50:00 0.41.1
2018-02-01:00:00.10.7
...
2018-02-28 24:00:00.10.7
What I want to do here is
Returns the maximum value of Value 2 every day and its time.
For example, in the above example, if the maximum value of Value 2 on 2/1 is 1.1, the query return value is
2018-02-01 00:30:00 0.51.1
I would like to be the case.
If Value2 has multiple 1.1s, it receives the value of the DateTime and Value1 fields as the first record to record the maximum value.
Here are some of the queries I tried:
SELECT FORMAT (DateTime, 'yyyyMMdd'), MAX (dblValue2)
FROM Table A
Group BY FORMAT (DateTime, 'yyyyMMdd')
With this, I could get the MAX value for each day, but I couldn't get the date when I recorded the MAX value.
Please let me know a good query to solve.
*I can solve this problem if I write programmable like C in WHILE below...
I would like to solve this problem with a query.
I haven't tried it yet...
SELECT TableA.DateTime, TableA.dblValue1, TableA.dblValue2
FROM (SELECT MIN (TableA.DateTime) ASD2
FROM (SELECT FORMAT (DateTime, 'yyyyyMMdd') ASD1, MAX (dblValue2) ASV1
FROM Table A
GROUP BY FORMAT (DateTime, 'yyyyyMMdd')
ASQ1
INNER JOIN TABLEA
ON FORMAT (TableA.DateTime, 'yyyyyMMdd') = Q1.D1 AND TableA.dblValue2 = Q1.V1
GROUP BY FORMAT (TableA.DateTime, 'yyyyMMdd')
ASQ2
INNER JOIN TABLEA
ON TableA.DateTime=Q2.D2
ORDER BY TABLEA.DateTime
© 2024 OneMinuteCode. All rights reserved.