Link to home
Start Free TrialLog in
Avatar of jvoconnell
jvoconnell

asked on

update column based on max value from mulitple rows

Experts,

I have a situation where I need to set a flag if the record is to be considered a current version of a claim.
A claim can have 3 adjustment codes. (0,1,2).

within the table, if a claim number has a max(adj_code) = 1 or 2 then the current version is = Y for all records with that claim number.

within the table, if a claim numbe has a max(adj_code) = 2 then the current_version = Y for all records with that claim number IF sum(pay_amt) <> 0 on the claims with and adj_Code in (1,2)

within the table, if a claim numbe has a max(adj_code) = 2 then the current_version = Y for ONLY the  record with adj_code = 2 IF sum(pay_amt) = 0 on the claims with and adj_Code in (1,2)


This would be the desired output:
since claim "BBB" records with an adj_code of 0 & 1 didnt sum to $0, only the record with the adj_Code = 2 is considered current.


CLAIM_NO   ADJ_CODE    PAY_AMT     PAID_DT     CURRENT_VERS

XXX         0           5           2/1/2012    N
XXX         1            -5         2/2/2012    N
XXX         2            8          2/4/2012    Y
BBB         0           7           3/1/2012    Y
BBB         1           -6          3/2/2012    Y
BBB         2           8           3/4/2012    Y
FFF         0           3           4/1/2012    Y

Is there an easier/more elequent way than using a bunch of temp tables.
I have attached a script with this sample data

any insight is appreciated
EE.sql
SOLUTION
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jvoconnell
jvoconnell

ASKER

I apologize. You are correct. I mis-typed the requirements. It should have been for adj_Code (0 &1). I'm sorry for taking up your time with the typo.


This is what it should have said.

within the table, if a claim numbe has a max(adj_code) = 2 then the current_version = Y for all records with that claim number IF sum(pay_amt) <> 0 on the claims with and adj_Code in (0 & 1 )

within the table, if a claim numbe has a max(adj_code) = 2 then the current_version = Y for ONLY the  record with adj_code = 2 IF sum(pay_amt) = 0 on the claims with and adj_Code in (0 & 1)


I'm going to see if I can leverage what you provided. Thanks!
>>within the table, if a claim number has a max(adj_code) = 1 or 2 then the current version is = Y for all records with that claim number.<<
Was this also a typo? Did you mean "if a claim number has a max(adj_code) = 0 or 1 then the current version is = Y for all records with that claim number." instead?
Yes awking00. You are correct. I am very sorry to waste peoples time with my question being inaccurate. I re-read it, may times but this has my head spinning.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you both for your input. If someone runs into this scenario in the future, I beleive that they could get to a solution uing either reponse as an example. I split the points as I agree with wpcortes that my quesion wasn't as clear as it should have been. But the reponse would direct me to solving my particular issue and was the first to respond. Awking00 helped sort through my example of the desired output and the unclear question I posed. I hope you both find splitting the points acceptable. I honestly appreciate both of you jumping in to help me. Thanks again.
No problem :-)