Good evening,
I'm trying to find the MAX value on the TOC column, my current query does show all the values within the date range. What I just want to achieve is to just display a row with the MAX value of the TOC and the date. Note: I'm doing this query into an INSQL server.
Any help would be greatly appreciated! Thanks in advance.
Here's the current query:
SELECT DateTime,
MAX(CASE WHEN TagName = 'WFI_LP01_TOC.PV' THEN value ELSE '' END) AS TOC
FROM runtime.dbo.History
WHERE TagName IN ('WFI_LP01_TOC.PV')
AND DateTime >= '2018-01-01'
AND DateTime <= '2018-01-08'
AND wwRetrievalMode = 'Cyclic'
GROUP BY DateTime
December 30, 2020 at 6:48 pm
So you want the TOC column to show the same value for all the rows within the selected range?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 30, 2020 at 6:53 pm
Greetings,
No, I just want a single row with the highest TOC column value in the selected range.
December 30, 2020 at 6:53 pm
My first comment on this is with the TagName appearing in the WHERE clause, you likely don't need to worry about the CASE statement in the MAX.
Looking at the result image and reading your description and query, I am not sure what is missing. Could you provide sample output of what you expect to come out of this query?
If you are meaning that you want the max value for the DATE and to exclude the time, one way to do this would be to use a CTE (or nested select) on your data to convert the DATETIME value DateTime to a DATE value. My opinion, this would be the easiest way assuming I am understanding your requirement correctly. Something like:
WITH cte AS (
SELECT CAST([DateTime] AS DATE) AS [Date],
[Value] AS [Value]
FROM [runtime].[dbo].[History]
WHERE [TagName] IN ('WFI_LP01_TOC.PV')
AND [DateTime] >= '2018-01-01'
AND [DateTime] <= '2018-01-08'
AND [wwRetrievalMode] = 'Cyclic'
)
SELECT [Date],
MAX([Value]) AS [TOC]
FROM cte
GROUP BY [Date]
NOTE - above code is untested as I do not have data to test it with.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
SELECT TOP (1) DateTime,
TOC = value
FROM runtime.dbo.History
WHERE TagName = 'WFI_LP01_TOC.PV'
AND DateTime >= '2018-01-01'
AND DateTime <= '2018-01-08'
AND wwRetrievalMode = 'Cyclic'
ORDER BY value desc
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 30, 2020 at 7:13 pm
Thanks a lot! This is exactly what I was looking for. Thanks for your time
December 30, 2020 at 7:17 pm
Sorry if I wasn't clear. I was looking for a query that shows only a single row with the MAX TOC value in the selected range. This code by Phil did the trick:
SELECT TOP (1) DateTime,
TOC = value
FROM runtime.dbo.History
WHERE TagName = 'WFI_LP01_TOC.PV'
AND DateTime >= '2018-01-01'
AND DateTime <= '2018-01-08'
AND wwRetrievalMode = 'Cyclic'
ORDER BY value desc
Thanks for your time!
January 4, 2021 at 12:33 pm
What is the data type of "value"?
It appears from your results that it might be numeric. But in your first attempt you use
value ELSE ''
in the CASE.
It appears that you don't need that CASE, since you're filtering on TagName.
But if "value" is a character string, your max may not come up with what you are expecting. For example, compare the max of
10.99 and 8.88
vs the max of
"10.99" and "8.88"
You would get different results.
January 4, 2021 at 3:09 pm
Hi,
Correct, data type is float. I found the answer with Phil, which don't use the CASE that I was using. Thanks for your time!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply