CODEWITHSUNDEEP
postgresqlfunctiondata-structures
Reza
Reza

Reputation: 113

Calculation in postgresql using functions

I have a table of organizations. The columns are: id int, name text, parent_id int.so it has a tree structure. I have another table, staff, with the fields id int, name txt, family txt, organization_id int. I have another table, clock, and the columns are: id int, staff_id int, Date date, Time time, which registers the clock in and clock outs.

For each organization in the tree (for each node) i want to calculate the working hours of its personnel. Finally i should sum up the working hours of children to get the working hours of its parent and so on.

So i need a function to do that. Can sb help?

Upvotes: 0

Views: 46

Answers (1)

FXD
FXD

Reputation: 2060

You do not need a function, use a recursive CTE.
If I correctly understood your schema and the meaning of the columns, this should look like this:

WITH RECURSIVE BrowseOrgs(init_id, id, name, parent_id) AS (
    SELECT id, id, name, parent_id
    FROM organizations
    /* Add WHERE clause here if needed */
    UNION ALL
    SELECT init_id, o.id, b.name, o.parent_id
    FROM organizations o
    JOIN BrowseOrgs b ON o.parent_id = b.id
)                       
SELECT init_id, b.name, date, SUM(time)
FROM BrowseOrgs b
JOIN staff s ON organization_id = b.id
JOIN clocks c on s.id = staff_id
GROUP BY init_id, b.name, date

Upvotes: 1

Related Questions