Count units by active and inactive dates

user_11227262 0
Viewed 126

I have a table with the following structure

table
unit      date_active  date_inactive
a         2018-01-01   NULL
b         2018-01-01   2020-07-05
c         2019-02-01   2020-01-01

I need a query to generate the total count of active units by month from date first active to today. The output needs to be along the lines of:

2018-01-01 2
2018-02-01 2
....

When date inactive is NULL of course the unit is still active today. I'm using PostgreSQL

postgresql sql
4 Answers

Generate series of dates, join it to the table and count units for each date:

select m::date as month, count(unit)
from generate_series('2018-01-01'::date, '2020-12-31', '1 month') m
left join my_table on m::date between date_active and coalesce(date_inactive, 'infinity')
group by 1
order by 1

Db<>Fiddle.

user_19957380

One option unpivots the rows, then aggregates:

select x.dt, sum(sum(x.cnt)) over(order by x.dt) cnt
from mytable t
cross join lateral (values
    (date_active, 1),
    (date_inactive, -1)
) x(dt, cnt)
where x.dt is not null
group by x.dt
order by x.dt
user_106767160

Do it like an inventory query.

Build an intermediate two-column table counters, with the start date and + 1 as the counter, UNION ALL the end date and -1 as the counter.

Over that intermediate table, run a running sum of the counters.

WITH
counters(dt,counter) AS (
  SELECT
    date_active
  , 1
  FROM input
  UNION ALL
  SELECT
    date_inactive 
  , -1
  FROM input
)
SELECT
  dt
, SUM(counter) OVER(ORDER BY ts) AS active_count
FROM counters;
user_67882550

Here is a complete similar scenario - only it's timestamp rather than date based... Ran it against PostgreSQL 12.4.

WITH
input(unit,ts_active,ts_inactive) AS (
          SELECT 'w',TIMESTAMP '2020-08-13 00:58:14.528778',TIMESTAMP '2020-08-13 00:58:14.552428'
UNION ALL SELECT 'w',TIMESTAMP '2020-08-13 00:59:32.022632',TIMESTAMP '2020-08-13 00:59:32.042015'
UNION ALL SELECT 'w',TIMESTAMP '2020-08-13 01:20:31.555526',TIMESTAMP '2020-08-13 01:20:31.599932'
UNION ALL SELECT 'w',TIMESTAMP '2020-08-13 01:23:55.585023',TIMESTAMP '2020-08-13 01:23:55.655960'
UNION ALL SELECT 'c',TIMESTAMP '2020-08-13 16:09:40.286047',TIMESTAMP '2020-08-13 16:09:40.332600'
UNION ALL SELECT 'c',TIMESTAMP '2020-08-13 16:09:51.887808',TIMESTAMP '2020-08-13 16:09:51.913150'
UNION ALL SELECT 'c',TIMESTAMP '2020-08-13 16:09:51.919182',TIMESTAMP '2020-08-13 16:09:52.023836'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:11:11.904413',TIMESTAMP '2020-08-13 16:11:11.921197'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:11:25.617580',TIMESTAMP '2020-08-13 16:11:26.084465'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:11:25.624842',TIMESTAMP '2020-08-13 16:11:25.631024'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:11:25.640343',TIMESTAMP '2020-08-13 16:11:25.647049'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:11:25.655991',TIMESTAMP '2020-08-13 16:11:25.662542'
UNION ALL SELECT 'i',TIMESTAMP '2020-08-13 16:11:25.691300',TIMESTAMP '2020-08-13 16:11:25.934098'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:11:25.958651',TIMESTAMP '2020-08-13 16:11:26.032297'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:11:26.076818',TIMESTAMP '2020-08-13 16:11:26.083056'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:23:23.779083',TIMESTAMP '2020-08-13 16:23:23.812426'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 16:23:34.150468',TIMESTAMP '2020-08-13 16:23:34.176461'
UNION ALL SELECT 't',TIMESTAMP '2020-08-13 17:30:16.483214',TIMESTAMP '2020-08-13 17:30:16.490638'
UNION ALL SELECT 'c',TIMESTAMP '2020-08-13 17:30:16.492756',TIMESTAMP '2020-08-13 17:30:16.505191'
UNION ALL SELECT 's',TIMESTAMP '2020-08-13 17:30:29.819721',TIMESTAMP '2020-08-13 17:30:29.957229'
)
,
counters(ts,counter) AS (
  SELECT
    ts_active
  , 1
  FROM input
  UNION ALL
  SELECT
    ts_inactive 
  , -1
  FROM input
)
SELECT
  ts
, SUM(counter) OVER(ORDER BY ts) AS active_count
FROM counters;
             ts             | active_count 
----------------------------+--------------
 2020-08-13 00:58:14.528778 |            1
 2020-08-13 00:58:14.552428 |            0
 2020-08-13 00:59:32.022632 |            1
 2020-08-13 00:59:32.042015 |            0
 2020-08-13 01:20:31.555526 |            1
 2020-08-13 01:20:31.599932 |            0
 2020-08-13 01:23:55.585023 |            1
 2020-08-13 01:23:55.65596  |            0
 2020-08-13 16:09:40.286047 |            1
 2020-08-13 16:09:40.3326   |            0
 2020-08-13 16:09:51.887808 |            1
 2020-08-13 16:09:51.91315  |            0
 2020-08-13 16:09:51.919182 |            1
 2020-08-13 16:09:52.023836 |            0
 2020-08-13 16:11:11.904413 |            1
 2020-08-13 16:11:11.921197 |            0
 2020-08-13 16:11:25.61758  |            1
 2020-08-13 16:11:25.624842 |            2
 2020-08-13 16:11:25.631024 |            1
 2020-08-13 16:11:25.640343 |            2
 2020-08-13 16:11:25.647049 |            1
 2020-08-13 16:11:25.655991 |            2
 2020-08-13 16:11:25.662542 |            1
 2020-08-13 16:11:25.6913   |            2
 2020-08-13 16:11:25.934098 |            1
 2020-08-13 16:11:25.958651 |            2
 2020-08-13 16:11:26.032297 |            1
 2020-08-13 16:11:26.076818 |            2
 2020-08-13 16:11:26.083056 |            1
 2020-08-13 16:11:26.084465 |            0
 2020-08-13 16:23:23.779083 |            1
 2020-08-13 16:23:23.812426 |            0
 2020-08-13 16:23:34.150468 |            1
 2020-08-13 16:23:34.176461 |            0
 2020-08-13 17:30:16.483214 |            1
 2020-08-13 17:30:16.490638 |            0
 2020-08-13 17:30:16.492756 |            1
 2020-08-13 17:30:16.505191 |            0
 2020-08-13 17:30:29.819721 |            1
 2020-08-13 17:30:29.957229 |            0
user_67882550
Related