SQLTeam.com | Weblogs | Forums

How to select previous row value for the current row


#1

//REAL
ACTIONID RATE
FX1 1.12
FX2 1.13
FX3 1.5

//INPUT
ACTIONID PREVIOUSACTIONID AMOUNT
FX1 NULL 100
FX2 FX1 200
FX3 FX2 300

//EXPECTED
ACTIONID RATE FLAG VARIANCE
FX1 CURRENT NO YES
FX2 PREVIOUS YES MAYBE
FX3 1.9 YES YES

//OUTPUT SHOULD BE Following

if RATE is 'CURRENT' then Pick the Rate from REAL
if RATE is 'PREVIOUS' then pick the previos ActionID (FROM INPUT) rate from REAL
else RATE is from EXPECTED, And all other field of EXPECTED Should also be retrieved.

ACTIONID RATE FLAG VARIANCE
FX1 1.12 NO YES
FX2 1.12 YES MAYBE
FX3 1.9 YES YES


#2

This means that EXPECTED.RATE is a mix of Text labels and Numerics, almost for sure this will lead to conversion errors when non-numeric values, stored as text, creep in. Better would be to have a second, numeric, column for RATE_VALUE when RATE_LABEL is not 'CURRENT' or 'PREVIOUS'

How do you locate the "previous row in [INPUT]"?

On FX2 row there is a previous row of FX1 in all three tables - is it OK to pick "FX1" from previous row in [EXPECTED] and then just JOIN that to [REAL] to get the rate (in which case the [INPUT] table is not required at all.

I therefore presume that that is NOT the case :slight_smile: and thus need example data (and expected results) which shows the situation where EXPECTED has rows that do not exist in INPUT, or vice versa, and so on.