teradata - SQL-- calculated columns using previous row -
i trying write sql create following table:
_row id cola colb colc mov_sum mult1 mult2 1 x 100 2 .05 102.5 null null 2 x 101 3 .1 206.6 null null 3 y a*206.6 b*206.6 .2 206.6*(a+b)+.2 b 4 y a*(206.6(a+b)+.2) b*(206.6(a+b)+.2) .4 (etc...) b
when id = 'x', cola, colb, , colc known , selected existing table. mov_sum calculated using previous row's mov_sum + cola + col b + colc.
when id = 'y', cola, colb , colc must calculated using previous row's sum_abc. multipliers "a" , "b" constant when id = 'y'.
i'm using teradata sql , cannot incorporate lag/lead syntax. have succeeded in calculating mov_sum (where id=x) creating intermediary table left join table on (where a._row >= b._row), sum(mov_sum) grouping _row.
i cannot figure out how incorporate when id= y, because cola &colb product of previous mov_sum, , not known values.
i thinking may require recursive query. suggestions?
Comments
Post a Comment