CODEWITHSUNDEEP
sql
Dmitriy_pechatnik
Dmitriy_pechatnik

Reputation: 37

count in one column

upd_date                id_sts     id_tsk
20/03/21 09:00        19          01
22/03/21 11:05        18          01
22/03/21 11:04        21          01
20/03/21 09:06        19          02
23/03/21 11:05        18          02
23/03/21 11:04        21          02

where id_sts 19 - open , 21 - in work and 18 close The user creates a task and closes it after a while. Example: On March 20, the user created a task, it is open, then on March 22, the task is closed. The question is, how can I display this period when the tasks on March 21 and March 22 are open. There are id_user, open, close, and update task columns in one column. I thought you could use with us or count the days until this task has the status open. the request is correct, but here is the total number of open issues.

select count(a.*), date_trunc('day', sh.upd_status)  from f.f_task a
join f.status_hist sh
on a.idtask = sh.idtask
where b.id_status not in (17,18) 
group by date_trunc('day', sh.upd_status)
order by date_trunc('day', sh.upd_status)

Upvotes: 0

Views: 45

Answers (1)

Serg
Serg

Reputation: 22811

A number of open tasks for every day in status_hist

select td.dupd, count(*) tasks_open
from (
  select distinct date_trunc('day', upd_date) dupd
  from status_hist) td
join (
  select id_tsk,
        max(case when id_sts = 19 then date_trunc('day', upd_date) end) dstart,
        max(case when id_sts = 18 then date_trunc('day', upd_date) end) dend,
  from status_hist
  group by id_tsk
) tsk on td.dupd between tsk.dstart and tsk.dend
group by td.dupd

Upvotes: 1

Related Questions