CODEWITHSUNDEEP
sql-serverdatetime
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

Find Average, MaxValue and MinValue and the Corresponding DateTime that the Values occur

I am working on a SQL query where I have to find out Average, Max value, Max Value-DateTime, Min Value, Min Value-DateTime of each Group of any Date. For this I used following query

SELECT
    Dl.TagDescID,
    convert( decimal(18,2), AVG( Dl.Value ) ) AS 'TotalAvgValue',
    convert(decimal(18,2), MAX( Dl.Value ) ) AS 'MaxValue',
    MAX( Dl.Date ) MaxDate,
    convert( decimal(18,2), MIN( Dl.Value ) ) AS 'MinValue',
    Min( Dl.Date ) MinDate
FROM
    tblDataLog AS Dl WITH (nolock) 
    INNER JOIN tblTagDescription AS TD WITH (nolock) ON Dl.TagDescID = TD.ID 
WHERE
    ( CONVERT(date, Dl.Date, 103) = CONVERT(date, @StartDate, 103) )
    AND
    ( TD.GroupName = @Group )
GROUP BY
    Dl.TagDescID
`

     TagDescID  TotalAvgValue   MaxValue    MaxDate MinValue    MinDate
       1    36.59   36.59   2020-01-07 10:13:42.293 36.59   2020-01-07 10:13:32.750
       2    20.49   20.49   2020-01-07 10:13:42.293 20.49   2020-01-07 10:13:32.750

In the above Data Set The MaxValue of TagDescID 1 is 36.59 and its Date as 'MaxDate' should be the exact datetime when this maxvalue Arrives in the table tblDataLog. here Max-Date of MaxValue of TagDescID 1 is '2020-01-07 10:13:42.293' but the value should have been '2020-01-07 08:13:42.293'. Same for MinValue and MinDate For each TagDescID.

How to solve it?

Upvotes: 0

Views: 310

Answers (2)

Chris Schaller
Chris Schaller

Reputation: 16554

The issue is that you are really asking for multiple aggregate and grouping sets at the same time. What you are observing is that each column aggregate expression is returning the result of that aggregate independently of the other columns, this is by design.

In the above query MaxDate and MinDate should be the Datetime when the MaxValue or MinValue inserted to the table tblDataLog.

In SQL Server we have a number of ways to query this.

One way is to query the set for the Max and Min values, then re-query the set for the Max and Min Dates around those times.

In this example I'm using a CTE instead of a simple nested query because I find the syntax more readable.

The two CROSS APPLY queries will evaluate once for each result in the Daily CTE. This solution can be more efficient to using an inline lookup for the DateTime of the corresponding log records because it doesn't have to lookup once for every row in tblDataLog, this solution will result in 2 additional lookups for every row that is returned by the group by.

;WITH Daily as
(
    SELECT  
        Dl.TagDescID, 
        convert(decimal(18,2),AVG(Dl.Value)) AS [TotalAvgValue],
        convert(decimal(18,2),MAX(Dl.Value)) AS [MaxValue], 
        convert(decimal(18,2),MIN(Dl.Value)) AS [MinValue] 
    FROM @tblDataLog AS Dl  
    INNER JOIN @tblTagDescription AS TD ON Dl.TagDescID = TD.ID 
    WHERE (CONVERT(date, Dl.Date, 103) = CONVERT(date, @StartDate, 103)) AND (TD.GroupName=@Group) 
    GROUP BY Dl.TagDescID  
)
SELECT Daily.TagDescID, TotalAvgValue, MaxValue, [Max].Date MaxDate, MinValue, [Min].Date MinDate
FROM Daily
CROSS APPLY (SELECT MAX(DlMax.Date) Date FROM @tblDataLog DlMax WHERE DlMax.TagDescID = Daily.TagDescId AND (CONVERT(date, DlMax.Date, 103) = CONVERT(date, @StartDate, 103)) AND DlMax.Value = Daily.MaxValue) as [Max]
CROSS APPLY (SELECT MIN(DlMin.Date) Date FROM @tblDataLog DlMin WHERE DlMin.TagDescID = Daily.TagDescId AND (CONVERT(date, DlMin.Date, 103) = CONVERT(date, @StartDate, 103)) AND DlMin.Value = Daily.MinValue) as [Min]

Another way to do this is to group by the date, instead of filtering for an explicit date value, so this will summarize by date and tag, giving you a report across multiple dates and multiple tags if you want it:

;WITH Daily as
(
    SELECT  
        Dl.TagDescID, 
        TD.GroupName,
        CONVERT(date, Dl.Date, 103) as Date,
        MAX(Dl.Date) as Max_Date,
        MIN(Dl.Date) as Min_Date,
        convert(decimal(18,2),AVG(Dl.Value)) AS [TotalAvgValue],
        convert(decimal(18,2),MAX(Dl.Value)) AS [MaxValue], 
        convert(decimal(18,2),MIN(Dl.Value)) AS [MinValue] 
    FROM @tblDataLog AS Dl  
    INNER JOIN @tblTagDescription AS TD ON Dl.TagDescID = TD.ID 
    GROUP BY Dl.TagDescID, TD.GroupName, CONVERT(date, Dl.Date, 103)
)
SELECT Daily.Date, Daily.GroupName, Daily.TagDescID, TotalAvgValue, MaxValue, [Max].Date MaxDate, MinValue, [Min].Date MinDate
FROM Daily
CROSS APPLY (SELECT MAX(DlMax.Date) Date FROM @tblDataLog DlMax WHERE DlMax.TagDescID = Daily.TagDescId AND DlMax.Date >= Daily.Min_Date AND DlMax.Date <= Daily.Max_Date) [Max]
CROSS APPLY (SELECT MIN(DlMin.Date) Date FROM @tblDataLog DlMin WHERE DlMin.TagDescID = Daily.TagDescId AND DlMin.Date >= Daily.Min_Date AND DlMin.Date <= Daily.Max_Date) [Min]
-- Optional Date Filter
-- WHERE TD.GroupName=@Group AND Daily.Date = @StartDate

In my test set, this returns:

Date        GroupName   TagDescID   TotalAvgValue   MaxValue    MaxDate                      MinValue   MinDate
2020-03-11  Test        1           5.00            8.00        2020-03-11  22:00:00.000     2.00       2020-03-11 10:00:00.000
2020-03-15  Test        1           5.00            8.00        2020-03-15  13:00:00.000     2.00       2020-03-15 06:00:00.000

Un-comment the where clause to enable your date filter, or you could change it to a date range and you would still see results by day.

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

I think this is what you are after. The corresponding Date when Value is minimum and maximum. You can use a sub-query to do this

SELECT
    Dl.TagDescID,
    convert( decimal(18,2), AVG( Dl.Value ) ) AS 'TotalAvgValue',
    convert(decimal(18,2), MAX( Dl.Value ) ) AS 'MaxValue',
    --MAX( Dl.Date ) MaxDate,
    (SELECT TOP 1 x.[Date] FROM  tblDataLog x WHERE x.TagDescID = Dl.TagDescID ORDER BY Value DESC) As MaxDate,
    convert( decimal(18,2), MIN( Dl.Value ) ) AS 'MinValue',
    --Min( Dl.Date ) MinDate
    (SELECT TOP 1 x.[Date] FROM  tblDataLog x WHERE x.TagDescID = Dl.TagDescID ORDER BY Value) As MinDate
FROM
    tblDataLog AS Dl WITH (nolock) 
    INNER JOIN tblTagDescription AS TD WITH (nolock) ON Dl.TagDescID = TD.ID 
WHERE
    ( CONVERT(date, Dl.Date, 103) = CONVERT(date, @StartDate, 103) )
    AND
    ( TD.GroupName = @Group )
GROUP BY
    Dl.TagDescID

EDIT : as others commented, avoid CONVERT(date, Dl.Date, 103) = CONVERT(date, @StartDate, 103)

You can replace that with

WHERE Dl.Date >= @StartDate
AND   Dl.Date <  DATEADD (DAY, 1, @StartDate)

Upvotes: 0

Related Questions