How to Calculate the Total under Recursive Relationships with esProc

A certain database has a ticket table and a work hour table. The ticket table stores the relationship between each ticket and its parent ticket, forming a self-association structure:
source table 1

The work hour table stores the direct working hours corresponding to each ticket:
source table 2

Now we need to recursively calculate the total working hours, which is the sum of the direct working hours of each ticket and the working hours of all subordinate sub tickets.
expected results

SQL does not support references, makes it inconvenient to express self-associated relationships, lacks recursive functions, and makes code difficult to write. SPL provides reference functions and can establish self-associations; Provides recursive functions that can take all subordinate nodes.

👉🏻 Try.DEMO
esProc SPL

A1： Join two tables and load data.

A2： Use the switch function to modify the field values of the parent ticket to the record references of the parent ticket, and establish a self-associated relationship. Record references can intuitively express parent-child relationships. The following figure shows the parent tickets of all levels of ticket 3, [8,20,26,30].

A3=A1.new(A1.nodes(parentID,~)) Create a new two-dimensional table and first calculate all the subordinate records of the current record. The function nodes can recursively calculate all the subordinate records of a certain record, where ~ represents the current record. The following figure shows all subordinate records of ticket No. 26, [3,8,14,20]:

A3=A1.new(ticketid,A1.nodes(parentID,~).sum(hours)+hours:Total_hours) Then calculate the total working hours of the current ticket, which is the sum of the working hours of the subordinate sub tickets plus the direct working hours.

Get Started with esProc SPL – esProc SPL FREE Download.