Crystal Reports: Formula to pull data point where other specific criteria are met -

i'm positive pretty easy formula i'm quite new cr , having difficult time this...searched answer couldn't find it, please forgive me if has been asked ad nauseam.

i have list of patients , each year patient assigned risk score, each patient has multiple scores. data looks this:

patient_id score_year risk_score 11111      2013       1.05 11111      2014       0.00 22222      2013       0.07 22222      2014       0.11 33333      2013       1.19 33333      2014       0.00 44444      2013       2.13 44444      2014       0.00 55555      2013       0.30 55555      2014       0.54 66666      2013       1.67 66666      2014       2.31 

i want create field assigns single risk score each patient can see data, patients have '0.00' 2014, don't want include.

my thought create formula states if [score_year] = 2014 , [risk_score] <> 0 ([risk_score] [score_year] = 2014) else ([risk_score] [score_year] = 2013) hitting brick wall.

in nutshell, need formula pull 2014 score first if 0 default 2013.

is there way write in formula or select case better? that's extent of cr knowledge, if guys know of more efficient i'd love learn!

the first 2 work sql expressions though it's possible might slow. major benefit calculated values part of data set , can used in passes of rendering process.

( select coalesce(max(score_year * 1000 + risk_score * 100.00) % 1000 / 100.00, 0.00) table t2 (score_year <> 2014 or risk_score <> 0.00) , t2.patient_id = t.patient_id ) -- better year(getdate()) - 1 instead of hardcoding 2014?? 

this 1 less of hack work on sql server (or maybe sybase). similar approaches work oracle, db2, etc.

coalesce(     (         select top 1 risk_score     table t2     risk_score <> 0.00 , t2.patient_id = t.patient_id     order score_year desc     ),     0.00 ) 

here crystal formula give correct value (i'm might rusty on syntax though.) need have crystal group on patient_id , put formula in detail section , suppress it. can use formula anywhere in group footer , correct value.

// @riskscore whileprintingrecords; static numbervar patient_id :: 0; static numbervar risk_score := 0.00; static numbervar score_year := 1900;      if (         risk_score = 0.00 , score_year < {t.score_year} or         patient_id <> {t.patient_id}     ) (         patient_id := {t.patient_id};         risk_score := {t.risk_score};         score_year := {t.score_year};     )     risk_score; 

following pattern of first sql expression convert pair of formulas. drawback here risk score not available use in row level (whilereadingrecords) formulas.

// @weightedriskscore {t.score_year} * 1000 + {t.risk_score} * 100.00;  // @mostrecentriskscore max(@weightedriskscore, "patient_id") mod 1000 / 100.00; 

depending on specifics of reports there possibly others options.