Complex Query to populate column

[paulnlk4]

To get difference between 2 rows and the resultant value into the latest row’s column.

Folks,

Here is how the data looks like:

Loan_table

AC_NUM DATE AMT DIFF

0001 12-31-2001 1000 0
0001 01-31-2002 3000 2000


In the above table, what I need to do is get the value from the AMT column for the MAX(DATE). Then I need to get the previous months AMT, in this case 1000, do a subtraction from current month (3000) – previous month (1000) and post the difference of 2000 in the last row with the max date. Can this be done by doing a JOIN or a subselect. Please post the SQL query. How do I go about accomplishing this?

All help will be greatly appreciated.

Paul

 

[BillDHS]

I will assume that since you are after a MAX date that there are multiple dates fro the same month in the table, and you only want the latest. I think you will need to do a derived table in the from clause to get the MAX date, then use that table for your final results:

SELECT t1.AC_Num, t1.Date, t1.AMT, MDiff(t1.AMT, 1, t1.Date)

FROM BaseTable t1 ,
(select AC_Num, syscalendar.month, MAX(Date)
from Basetable
group by syscalendar.month)t2.(AC, amonth, MDate)
WHERE t1.AC_Num = t2.AC
and t1.Date = t2.MDate

 

[paulnlk4]

Bill,

No, there are no multiple records in the same month. Only one per month with the same AC_NUM, however what I want to do is post the difference between current months AMT and the previous months AMT in the DIFF column.

For eg: If AC_NUM did not exist in Nov, 2001 and in Dec, 2001 the AMT was 1000 and again in Jan, 2002 the amount was 3000, I want to subtract AMT(Jan2002) - AMT(DEC2001) and post the resulting difference 2000 in the row belonging to JAN, 2002. Will your above query be able to do this?

Thanks,
Paul

 

[BillDHS]

Yes, I think so, look at the MDIFF function in Teradada.

SELECT t1.AC_Num,
t1.Date,
t1.AMT,
MDiff(t1.AMT, 1, t1.Date)as Balance

FROM BaseTable t1

 

[paulnlk4]

Thanks Bill, I think you have answered by question.