Calculation in postgresql using functions

Question

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?

If you can post some sample data, desired output and any code you have thus far, I'm betting someone here will be able to help you with your problem. — Hambone
– Hambone, Commented Jan 11, 2019 at 22:17

FXD · Accepted Answer · 2019-01-12 11:30:17Z

1

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

answered Jan 12, 2019 at 11:30

FXD

2,0901 gold badge10 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calculation in postgresql using functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related