...

6.3 Ordinary grouping: by ordinal numbers

 
spl-cookbook(224)

Group records and perform a computation in each group according to the ordinal numbers. Here’s a table recording daily attending information:

Per_Code in_out Date Time Type
1110263 1 2013-10-11 09:17:14 In
1110263 6 2013-10-11 11:37:00 Break
1110263 5 2013-10-11 11:38:21 Return
1110263 0 2013-10-11 11:43:21 NULL
1110263 6 2013-10-11 13:21:30 Break
1110263 5 2013-10-11 14:25:58 Return
1110263 2 2013-10-11 18:28:55 Out

The task is to group the table by every 7 records and transform each group of records into the following format:

Per_Code Date In Out Break Return
1110263 2013-10-11 9:17:14 18:28:55 11:37:00 11:38:21
1110263 2013-10-11 9:17:14 18:28:55 13:21:30 14:25:58

Create the target data structure, arrange every seven records into the desired format, and insert the ready data to the structure. A()function and A.m() function are used to access one or more members according to the ordinal number(s).

SPL script:

A
1 =connect(“db”)
2 =A1.query@x(“select * from DailyTime order by Per_Code,Date,Time”)
3 =A2.group@o((#-1)\7)
4 =create(Per_Code,Date,In,Out,Break,Return)
5 =A3.(~([1,7,2,3,1,7,5,6]))
6 =A5.conj([~.Per_Code,~.Date]|~.(Time).m([1,2,3,4])|[~.Per_Code,~.Date]|~.(Time).m([5,6,7,8]))
7 >A4.record(A6)

A1 Connect to the data source.
A2 Query data and sort it by personnel code, date and time.
A3 Group A2 by personnel code and date.
A4 Create an empty table sequence for storing the final result.
A5 From each group, get records one by one according to A([1,7,2,3,1,7,5,6]). This is the ordered records of the whole day.
A6 Organize data in all records into one sequence. A.m() is used to access multiple members.
A7 Fill sequence data in the table sequence created in A4.

Execution result:

Per_Code Date In Out Break Return
1110263 2013-10-11 09:17:14 18:28:55 11:37:00 11:38:21
1110263 2013-10-11 09:17:14 18:28:55 13:21:30 14:25:58
… … … … … …
spl-cookbook(224)
 
 •  194 View  •