EVAL_PERIOD
PAID
OVERALL_TOTAL
THRESHOLD_AMT
final
2016-05
21954.84
110699.68
10349.84
10349.84
2016-06
42005.43
152705.11
31352.555
21002.715
2016-07
13980.58
166685.69
38342.845
6990.29
2016-08
41929.79
208615.48
59307.74
20964.895
2016-09
487.33
209102.81
59551.405
243.665
I want to create the ‘final’ column in Toad. I did this in Excel. Basically what I am doing is taking the threshold_amt and taking the difference from the previous ‘final’ amounts. For example, 31352.555-10349.84 = 21002.715. The next one is 38342.845-(21002.715+10349.84)… and so on and so on. But I have to make sure that I am subtracting the results in the above cells… I hope this makes sense… Anything y’all can do to help, would be greatly appreciated.
More a SQL question than a Toad question but it only took a couple of minutes to mock up so here you go:
Create Table MG_TOTS_TEST
(
EVAL_PERIOD Date,
PAID Number,
OVERALL_TOTAL Number,
THRESHOLD_AMT Number
);
Insert Into MG_TOTS_TEST
Values (
‘01-MAY-2015’,
21954.84,
110699.68,
10349.84
);
Insert Into MG_TOTS_TEST
Values (
‘01-JUN-2016’,
42005.43,
152705.11,
31352.555
);
Insert Into MG_TOTS_TEST
Values (
‘01-JUL-2016’,
13980.58,
166685.69,
38342.845
);
Insert Into MG_TOTS_TEST
Values (
‘01-AUG-2016’,
41929.79,
208615.48,
59307.74
);
Insert Into MG_TOTS_TEST
Values (
‘01-SEP-2016’,
487.33,
209102.81,
59551.405
);
Commit;
Select Eval_Period,
Paid,
Overall_Total,
Threshold_Amt,
Threshold_Amt - Nvl( Prev_Threshold, 0 ) Final
From (
Select Eval_Period,
Paid,
Overall_Total,
Threshold_Amt,
Lag( Threshold_Amt ) Over ( Order By Eval_Period ) Prev_Threshold
From MG_TOTS_TEST
);
Mark.