CODEWITHSUNDEEP
sqlpostgresql
Andreq Frenkel
Andreq Frenkel

Reputation: 1208

Calculate count of each status in date range

I’m using PostgreSQL and I try to realize how to build the query for calculating the count of each status of event per date in the range

My event table looks like:


| dateStart  | dateEnd    | status |
|----------------------------------|
| 2019-02-10 | 2019-02-11 | Type1  |
| 2019-02-10 | 2019-02-16 | Type1  | 
| 2019-02-10 | 2019-02-12 | Type2  |
| 2019-02-11 | 2019-02-16 | Type1  |
| 2019-02-12 | 2019-02-14 | Type2  |
| 2019-02-13 | null       | Type2  |
| 2019-02-12 | 2019-02-13 | Type2  |
| 2019-02-15 | 2019-02-16 | Type3  |
| 2019-02-14 | 2019-02-14 | Type1  |

Event starts from dateStart and ends by dateEnd (or null if it is still in progress) and have to be counted in every date in range if dateStart-dateEnd somehow cross query date range.

I have a date range, for example:

from: 2019-02-12 to: 2019-02-15

Actually I want to get such result for that range:

| date       | type1Count | type2Count | type3Count |
|---------------------------------------------------|
| 2019-02-12 | 2          | 3          | 0          | 
| 2019-02-13 | 2          | 3          | 0          | 
| 2019-02-14 | 3          | 2          | 0          | 
| 2019-02-15 | 2          | 1          | 1          |

This picture demonstrates idea:

enter image option here

That's what I have currently, but it only uses the dateStart

  SELECT DATE_TRUNC('day', "events"."dateStart") AS dateStart,
          COUNT(CASE WHEN "events"."status" = 'Type1' THEN 1 END) AS "Type1",
          COUNT(CASE WHEN "events"."status" = 'Type2' THEN 1 END) AS "Type2",
          COUNT(CASE WHEN "events"."status" = 'Type3' THEN 1 END) AS "Type3",
        FROM "events"
        GROUP BY 1
        ORDER BY 1;

I will appreciate any help

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The simplest method is Postgres is use to generate_series():

select d.dte, count(e.type) as total,
       count(*) filter (where e.type = 'Type1') as type1_cnt,
       count(*) filter (where e.type = 'Type2') as type2_cnt,
       count(*) filter (where e.type = 'Type3') as type3_cnt
from (select generate_series(min(datestart), max(datestart), interval '1 day') as dte
      from events
     ) d left join
     events e
     on d.dte >= e.datestart and d.dte <= e.dateend
group by d.dte
order by d.dte;

Upvotes: 1

Related Questions