January 4, 2021 at 3:57 pm
Good morning,
I'm trying to get the Temp value at the maximum value of the Cond column. The red line in the first query is what I'm expecting, the second one its the result I'm getting at the moment.
Current query:
SELECT TOP (1) DateTime, CASE WHEN TagName = 'WFI_LP01_Cond.PV' THEN value END AS Cond, CASE WHEN TagName = 'WFI_LP01_Temp.PV' THEN value END AS TempAtMaxConductivity
FROM runtime.dbo.History
WHERE TagName IN ('WFI_LP01_Cond.PV', 'WFI_LP01_Temp.PV')
AND DateTime >= '2018-01-01'
AND DateTime <= '2018-01-08'
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 3600000
ORDER BY Cond desc
Any help would be greatly appreciated!
January 4, 2021 at 6:04 pm
I imagine that if you replace this
CASE WHEN TagName = 'WFI_LP01_Temp.PV' THEN value END AS TempAtMaxConductivity
with this
TempAtMaxConductivity = value
it will solve the problem.
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.
January 4, 2021 at 6:19 pm
Greetings,
I've tried it but isn't the result I was expecting
January 4, 2021 at 7:24 pm
Not a lot of details to go on, so I'll have to assume that the time is the same for the two rows that need brought together.
SELECT TOP (1) DateTime,
MAX(CASE WHEN TagName = 'WFI_LP01_Cond.PV' THEN value END) AS Cond,
MAX(CASE WHEN TagName = 'WFI_LP01_Temp.PV' THEN value END) AS TempAtMaxConductivity
FROM runtime.dbo.History
WHERE TagName IN ('WFI_LP01_Cond.PV', 'WFI_LP01_Temp.PV')
AND DateTime >= '2018-01-01'
AND DateTime <= '2018-01-08'
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 3600000
GROUP BY DateTime
ORDER BY Cond desc
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 4, 2021 at 7:43 pm
Ok, how about
TempAtMaxConductivity = temp
?
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.
January 10, 2021 at 9:12 am
I think you do not need the "Group by":
SELECT TOP (1) DateTime,
MAX(CASE WHEN TagName = 'WFI_LP01_Cond.PV' THEN value END) AS Cond,
MAX(CASE WHEN TagName = 'WFI_LP01_Temp.PV' THEN value END) AS TempAtMaxConductivity
FROM runtime.dbo.History
WHERE TagName IN ('WFI_LP01_Cond.PV', 'WFI_LP01_Temp.PV')
AND DateTime >= '2018-01-01'
AND DateTime <= '2018-01-08'
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 3600000
--GROUP BY DateTime
ORDER BY Cond desc
January 10, 2021 at 5:28 pm
I guess I am not seeing the problem - wouldn't just using TOP 1 with ORDER BY Cond desc return that one row? Why the need for a case expression?
Unless the values you are trying to return are coming from separate rows...then there isn't any reason to use CASE to identify which tagname the value is coming from. If these values are coming from different rows...then you would need some way to correlate the rows to identify the associated Temp to a specific Cond.
With the given information - either the CASE expression isn't needed...or the correlation is not defined.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply