# java computation issue

• August 5th, 2013, 02:02 AM
datathinker
java computation issue
Question background:

One Bank Information System needs to figure out mobile average(MA)( On the chart, the mobile average is a trend line which smoothes the recurrences of the days and provides you with a quick overview of the period trend) amount of monthly loan from Loan Table defined as below,

 Time amount … 3/14/2011 \$43,334.10 … 3/15/2011 \$92,304.10 ... 3/16/2011 \$45,983.80 ... 3/17/2011 \$36,973.10 ... 3/18/2011 \$24,987.87 ... ... ... ...
It is easy for SQL to calculate monthly loan by grouping by. But I don't know how to make it out with sql as relative location and cross row computation are involved , Soppose we do it purely with java, following the codes:
Code java:

```st=conn.prepareStatement("select sum(amount)amount, to_char(time,'MM')month from loan where to_char(time,'yyyy')=to_char(sysdate,'yyyy') group by to_char(time,'MM') order by month",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs=st.executeQuery(); int size=rs.getFetchSize(); for(int currentPos=1;currentPos<=size;currentPos++){ rs.absolute(currentPos); float preAmount=-1,thisAmount=-1,nextAmount=-1; float avgAmount=-1; String month=rs.getString("month"); thisAmount=rs.getFloat("amount"); if(currentPos==1){ rs.next(); nextAmount=rs.getFloat("amount"); avgAmount=(thisAmount+nextAmount)/2; }else if(currentPos==size){ rs.previous(); preAmount=rs.getFloat("amount"); avgAmount=(thisAmount+preAmount)/2; }else{ rs.previous(); preAmount=rs.getFloat("amount"); rs.next(); rs.next(); nextAmount=rs.getFloat("amount"); avgAmount=(thisAmount+nextAmount+preAmount)/3; } System.out.println(month+" "+avgAmount); } rs.close();```
....
Except pure java coding, I think this kind of data process job could also be done by sql or esProc and maybe esProc will be easier. But I don't know too much about sql, hope people viewing my thread could give some tips.
• August 5th, 2013, 08:25 AM
KevinWorkman
Re: java computation issue
Are you asking how to do this using SQL? This is a Java forum, not a SQL forum.

I can move this thread to the "other languages" forum and hope somebody knows more about SQL if you want, but I'd suggest posting on a SQL forum instead. Make sure you link between crossposts though.