Problem source：http://bbs.csdn.net/topics/391012377 .
Below is a selection from the original table (tb1):
Prjno Subtask Ddate Num
P9996 P9996-sub002 2015-01-01 123
P9996 P9996-sub002 2015-01-02 134
P9996 P9996-sub002 2015-01-03 345
P9996 P9996-sub002 2015-01-04 55
T0071 T-007-01 2015-01-01 3333
T0071 T-007-01 2015-01-02 356
T0071 T-007-01 2015-01-03 178
According to a specified date, you need to get all projects before this date in the same month. Suppose the input date is 2015-01-03, you’ll get this:
Prjno Subtask 2015-01-01 2015-01-02 2015-01-03
P9996 P9996-sub002 123 134 345
T0071 T-007-01 3333 356 178
esProc code for doing this:
A1: Query data from the beginning of the month to the specified date. d_date is an input date parameter, like 2015-01-03. pdate@m(d_date) calculates the first date of the current month.
A2: Create an empty result table sequence with dynamic columns according to the sequence of dates from the first date of the month to the specified date:
A3: The first part A1.group(Prjno,Subtask) groups A1’s data by Prjno and Subtask (esProc data grouping will keep the detail data of each group), then ~.groups(Ddate;sum(Num):Num), one by by, groups each group of data by the date and aggregate Num valules; finally, A2.record() writes each group name and the aggregate value into A2’s result table sequence. The following is the final result:
In a similar way any database is called, esProc can be called by the reporting tool or a JAVA program. The computed result in the form of ResultSet can be returned to JAVA main program via esProc JDBC. You can see related documents for detailed method.