Pgsql sum amount within the same interval of date

user_5647122 0
Viewed 66

I'm a fairly new Postgres user, I'm sure there's an answer to this already but I can't find it.

I have three tables:

Tasks :

| id  | start_date |  end_date  |
| --- | ---------- | ---------- |
|1    | 2021-01-27 | 2021-03-27 |
|2    | 2021-02-21 | 2021-04-27 |

Employe :

| id  | name           | 
| --- | -------------- |
| 1   | Linus Torvalds |

Employe_task:

| id  | employe_id| task_id|amount|
| --- | --------- |------- |----- |
|  1  |     1     |   1    |   2  |
|  2  |     1     |   2    |   1  |

I need to sum quantity within the same interval and calculate the start_date and end_date.

I'm looking for a query to get this result:

| amount |    name      | start_date|end_date  |
| ------ | ------------ |---------- |--------- |
| 2      |Linus Torvalds| 2021-01-27|2021-02-21|
| 3      |Linus Torvalds| 2021-02-21|2021-03-27|
| 1      |Linus Torvalds| 2021-03-27|2021-04-27|

I created a sqlfiddle to make it simpler.

postgresql
1 Answers

The easiest way is to split your task to smaller queries (CTEs) and then write simple query on those CTEs, see below query:

with cte as (
  select start_date as dt from tasks
  union all
  select end_fin from tasks
), tasks_intervals as (
  select dt as start_dt, lead(dt) over (order by dt) as end_dt from cte
), task_employee_details as (
  select et.amount, e.name, t.start_date, t.end_fin from employe_task et
  join employes e on et.employe_id = e.id
  join tasks t on et.task_id = t.id
)

select ted.name, sum(ted.amount) as amount, ti.start_dt, ti.end_dt
from task_employee_details ted
join tasks_intervals ti on
  ted.start_date <= ti.start_dt and
  ted.end_fin >= ti.end_dt
group by ted.name, ti.start_dt, ti.end_dt

DB fiddle

user_71325500
Related