sqlsql-servergroup-bydatefield

Get multiple max values sorting date time

Here is my sample data.
DateAndTime column measured per 10 seconds.
Date range is 6/30 ~ 8/31.
[DateAndTime], [TagName] is varchar(50).

DateAndTime             TagName        DataValue
2022-06-30 14:15:40    BW004_GD-4-16   99
2022-06-30 14:15:50    BW004_GD-4-16   25
2022-06-30 14:16:00    BW004_GD-4-16   99
2022-06-30 14:16:10    BW004_GD-4-16   50
2022-06-30 14:16:20    BW004_GD-4-16   99
2022-06-30 14:16:30    BW004_GD-4-16   99
.
.
.
2022-06-30 14:15:40    BW004_GD-4-17   50
2022-06-30 14:15:50    BW004_GD-4-17   40
2022-06-30 14:16:00    BW004_GD-4-17   25
.
.
.
2022-06-30 18:20:00    BW004_GD-4-17   50
2022-06-30 18:20:10    BW004_GD-4-17   50
2022-06-30 18:20:20    BW004_GD-4-17   10
.
.
.
2022-06-30 14:15:40    BW004_GD-4-18   30
2022-06-30 14:15:50    BW004_GD-4-18   40  
2022-06-30 14:16:00    BW004_GD-4-18   100
.
.
.

Here is problem.

  1. DateAndtime is CharField, not date field.
  2. maximum datavalue is duplicated.

I want to extract the maximum and earliest datetime of each tag by day of the week sorted by DateAndTime.

Result example:

DateAndTime            TagName         MaxValue  
2022-06-30 14:15:40    BW004_GD-4-16   99
2022-06-30 14:15:40    BW004_GD-4-17   50
2022-06-30 14:16:00    BW004_GD-4-18   100
.
.
.


SELECT LEFT([DateAndTime], 10) ,
    [TagName]
    , MAX([DataValue]) AS MaxValue
FROM [RTDB].[dbo].[Env_AI]
GROUP BY LEFT([DateAndTime], 10), [TagName]
ORDER BY [TagName]

This SQL works but does not include time.

Please help me. Thank you.

Solution

  • Use dense_rank() to find the rows with maximum DataValue (partition) by Date and TagName

    select *
from (
    select *,
        rn = dense_rank() over (
            partition by left(DateAndTime, 10), 
            TagName 
            order by DataValue desc
        )
    from RTDB.dbo.Env_AI
) t
where rn = 1