CODEWITHSUNDEEP
postgresqlcount
kimi
kimi

Reputation: 525

Running count of working days in Postgres

How can I get the number of productive days as below in Postgres?

In sample data, there are 29 days and 21 working days. Productive days are the days when owner_name has a call.

+------------+-------------+---------------+-----------------+-------------+
|    date    | owner_name  | working_days | productive_days | Total_calls |
+------------+-------------+---------------+-----------------+-------------+      
| 11/29/2019 | James Jones |            21 |              18 |          78 |
| 11/28/2019 | James Jones |            20 |              17 |         725 |
| 11/27/2019 | James Jones |            19 |              16 |         424 |
| 11/26/2019 | James Jones |            18 |              15 |          42 |
| 11/25/2019 | James Jones |            17 |              14 |          98 |
| 11/24/2019 | James Jones |               |                 |           0 |
| 11/23/2019 | James Jones |               |                 |           0 |
| 11/22/2019 | James Jones |            16 |              13 |          55 |
| 11/21/2019 | James Jones |            15 |              12 |         142 |
| 11/20/2019 | James Jones |            14 |              11 |         346 |
| 11/18/2019 | James Jones |            12 |              10 |          47 |
| 11/15/2019 | James Jones |            11 |                 |           0 |
| 11/14/2019 | James Jones |            10 |                 |           0 |
| 11/13/2019 | James Jones |             9 |               9 |         754 |
| 11/12/2019 | James Jones |             8 |               8 |          78 |
| 11/11/2019 | James Jones |             7 |               7 |          74 |
| 11/10/2019 | James Jones |               |                 |           0 |
| 11/9/2019  | James Jones |               |                 |           0 |
| 11/8/2019  | James Jones |             6 |               6 |          78 |
| 11/7/2019  | James Jones |             5 |               5 |          75 |
| 11/6/2019  | James Jones |             4 |               4 |          74 |
| 11/5/2019  | James Jones |             3 |               3 |         424 |
| 11/4/2019  | James Jones |             2 |               2 |         424 |
| 11/3/2019  | James Jones |               |                 |           0 |
| 11/2/2019  | James Jones |               |                 |           0 |
| 11/1/2019  | James Jones |             1 |               1 |          24 |
+------------+-------------+---------------+-----------------+-------------+

Upvotes: 1

Views: 56

Answers (1)

forpas
forpas

Reputation: 164164

With count() window function:

select *,
  case when "Total_calls" <> 0 
    then count(case when "Total_calls" <> 0 then "Total_calls" end) over (partition by owner_name order by date) 
  end  
from tablename
order by owner_name, date desc

See the demo.
Results:

| date       | owner_name  | working_days | Total_calls | productive_days |
| -----------| ----------- | ------------ | ----------- | --------------- |
| 2019-11-29 | James Jones | 21           | 78          | 18              |
| 2019-11-28 | James Jones | 20           | 725         | 17              |
| 2019-11-27 | James Jones | 19           | 424         | 16              |
| 2019-11-26 | James Jones | 18           | 42          | 15              |
| 2019-11-25 | James Jones | 17           | 98          | 14              |
| 2019-11-24 | James Jones |              | 0           |                 |
| 2019-11-23 | James Jones |              | 0           |                 |
| 2019-11-22 | James Jones | 16           | 55          | 13              |
| 2019-11-21 | James Jones | 15           | 142         | 12              |
| 2019-11-20 | James Jones | 14           | 346         | 11              |
| 2019-11-18 | James Jones | 12           | 47          | 10              |
| 2019-11-15 | James Jones | 11           | 0           |                 |
| 2019-11-14 | James Jones | 10           | 0           |                 |
| 2019-11-13 | James Jones | 9            | 754         | 9               |
| 2019-11-12 | James Jones | 8            | 78          | 8               |
| 2019-11-11 | James Jones | 7            | 74          | 7               |
| 2019-11-10 | James Jones |              | 0           |                 |
| 2019-11-09 | James Jones |              | 0           |                 |
| 2019-11-08 | James Jones | 6            | 78          | 6               |
| 2019-11-07 | James Jones | 5            | 75          | 5               |
| 2019-11-06 | James Jones | 4            | 74          | 4               |
| 2019-11-05 | James Jones | 3            | 424         | 3               |
| 2019-11-04 | James Jones | 2            | 424         | 2               |
| 2019-11-03 | James Jones |              | 0           |                 |
| 2019-11-02 | James Jones |              | 0           |                 |
| 2019-11-01 | James Jones | 1            | 24          | 1               |

Upvotes: 1

Related Questions