Value comparison

Question

Value comparison

cory.bullard76

Ten Centuries

Points: 1232
More actions
January 22, 2019 at 2:23 pm

#381256

I have a query that's returning prescription status. Here's the deal. If a patient has 3 different prescriptions and all 3 are Discontinued...I want all 3 scripts to show.
But lets say a patient has 3 scripts (2 discontinued, 1 active)...I want to look at the date to pull what the most recent is.
In a nutshell....if all statuses are the same---pull the Rx. If the statuses are different...pull the most recent.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

cory.bullard76 - Tuesday, January 22, 2019 2:23 PM
I have a query that's returning prescription status. Here's the deal. If a patient has 3 different prescriptions and all 3 are Discontinued...I want all 3 scripts to show.
But lets say a patient has 3 scripts (2 discontinued, 1 active)...I want to look at the date to pull what the most recent is.
In a nutshell....if all statuses are the same---pull the Rx. If the statuses are different...pull the most recent.

You could use ROW_NUMBER to identify the most recent and windowing functions to filter the data. You want to return where min_script_value = max_script_value OR row_num_col = 1.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 2

Thanks! And that would return all values if the statuses are the same?

drew.allen SSC Guru Points: 76949 More actions · Answer 3

cory.bullard76 - Tuesday, January 22, 2019 2:23 PM
I have a query that's returning prescription status. Here's the deal. If a patient has 3 different prescriptions and all 3 are Discontinued...I want all 3 scripts to show.
But lets say a patient has 3 scripts (2 discontinued, 1 active)...I want to look at the date to pull what the most recent is.
In a nutshell....if all statuses are the same---pull the Rx. If the statuses are different...pull the most recent.

And what do you want to do if 1 is discontinued and 2 are active? Wouldn't you want to return both of the active ones? If so, you'll want to use RANK() instead of ROW_NUMBER().

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 4

Thanks Drew....if the statuses are different I want to return the most recent Rx (based on DoseStartDate). If all the statuses are the same I want to return all the Rxs