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: 246950 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 101084 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

Ok, how about

TempAtMaxConductivity = temp

?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

cyrusbaratt SSC-Addicted Points: 417 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: 90303 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