Total under Recursive Relationship — From SQL to SPL #33

#programming #database #sql #esprocspl

Problem Description & Analysis:

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:

The work hour table stores multiple working hours corresponding to each ticket.
Click here to check out the Work Hour Table.

Task: Now we need to calculate the working hours for each ticket and recursively calculate the sum of the working hours for that ticket and all subordinate tickets, which is the total working hours.

Code Comparisons:

SQL：

WITH    CTE_TREE AS (
        SELECT  parentid AS parentid, ticketid AS children
        FROM    tickets t
        WHERE   parentID <> 0
        UNION
        SELECT  parentid, NULL
        FROM    tickets
        WHERE   parentID <> 0
        UNION
        SELECT  ticketid, NULL
        FROM    tickets
    )
    ,   CTE_TRAVERSE AS (
        SELECT  parentid AS mainId, children AS nextParent
        FROM    CTE_TREE
        UNION ALL
        SELECT  t.mainId, tree.children
        FROM    CTE_TREE tree
        INNER JOIN CTE_TRAVERSE t
            ON  t.nextParent = tree.parentid
        WHERE   tree.children <> ''
    )
    SELECT  t.MainID
    ,   SUM(CASE WHEN t.nextparent IS NULL THEN h.Hours END) AS Direct_hours
    ,   SUM(h.Hours) AS Total_hours
    FROM    CTE_TRAVERSE t
    INNER JOIN Hours h
        ON  h.ticketid = t.nextparent
        OR  (h.ticketid = t.mainID AND t.nextparent IS NULL)
    GROUP BY t.mainId

SQL requires multiple subqueries to implement self-association and recursive relationships, and the code is complex and difficult to understand.

SPL: SPL directly provides reference functions to establish self-associations and recursive functions to take all subordinate nodes:

🧩 Try.DEMO

A1： Load data and calculate the direct working hours for each ticket based on the ticket table.

A2： Set the parent ticket field of each record to point to the parent ticket record and establish a self-association relationship. The switch function can modify field values to records.

A3： Create a new two dimensional table, where the total working hours of the current ticket are equal to the direct working hours of the current ticket plus the sum of the working hours of all its subordinate sub tickets. The function nodes can recursively calculate all the subordinate records of a certain record.

Free to Try, Powerful to Use — esProc SPL FREE Download. ✨🚀