Last Column value at the maximum value of Cond Column

Question

Post reply

Last Column value at the maximum value of Cond Column

tripari

SSC Enthusiast

Points: 189

January 4, 2021 at 3:57 pm

#3827136

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.

Screenshot (23)_LI

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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

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.

tripari SSC Enthusiast Points: 189 More actions · Answer 2

Greetings,

I've tried it but isn't the result I was expecting

Screenshot (25)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

Phil Parkin SSC Guru Points: 246693 More actions · Answer 4

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.

cyrusbaratt Old Hand Points: 346 More actions · Answer 5

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

DBASupport

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 6

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs