"【Question】 According to a date specified by the parameter, query data before this date in the cu .."

blackduckie RaqForum 28 No.
587 View • 5 Years ago

Generating a Result Set with Dynamic Columns

SQL(179)

【Question】

According to a date specified by the parameter, query data before this date in the current month.

Suppose the value passed to the parameter is 20150103, query the man-hours each day before the specified date in the current month. If the passed date condition is 20150110, then 10 columns for 10 days will be generated.

Below is the report I want to display (columns from the third are dynamic):
Prjno    Subtask         20150101    20150102    20150103...
P9996   P9996-sub002    128         200         150
T0071   T-007-01        40          100         100
.......
Is there any simple approach to do this?

【Answer】

The result set has dynamic columns. It’s hard to compose the query in SQL. But it’s easy to write it in SPL, which has dynamic syntax.

	A
1	=oracle.query("select * from project where Ddate>=? and Ddate<=?", pdate@m(d_date) ,d_date)
2	=${"create(Prjno,Subtask,"+periods(pdate@m(d_date),d_date,1).concat@c()+")"}
3	>A1.group(Prjno,Subtask).run(A2.record( ~.Prjno \| ~.Subtask \| ~.group(Ddate).(~.sum(Num))))

Before executing the SPL script, save the original data in the project table in database and add oracle data source in esProc Datasource manager (For more information, see http://doc.raqsoft.com/esproc/tutorial/sjkpzh.html)

SPL script explanation:

A1: oracle is the data source object. query() function gets data according to the condition. d_date is a cellset parameter, which can be set in Program->Parameter on esProc designer;

undefined

A2: Create a table sequence for storing the final result;

A3: A1.group(Prjno,Subtask) groups the original data in A1 by Prjno and Subtask fields. The other part performs grouping and aggregate operations to generate the desired records and store them in A2. Below is the query result we want:

undefined