Explode and Count all items from 2 dates column

Question

I would like to get all possible date (in this case : event_day) and number of event that happen between start_date and end_date. please look table below

 ---------------------------------
 start_date |  end_date   |  event
 ---------------------------------
 2019-01-01 |  2019-01-04 | A 
 2019-01-02 |  2019-01-03 | B
 2019-01-01 |  2019-01-06 | C

and I want to query to get number of event_count in all date. please see the following result

----------------------------
event_day |  event_count
----------------------------
2019-01-01 |   2
2019-01-02 |   3
2019-01-03 |   3
2019-01-04 |   2
2019-01-05 |   1
2019-01-06 |   1

I read others source but can only find how to explode date from 2 dates. Any helps here? Thanks

Please tag your question with the DBMS you use. – Amir Molaei Apr 23 '19 at 06:16 — Amir Molaei, Apr 23 '19 at 06:16

Sebastian Brosch · Accepted Answer · 2019-04-23T06:32:32.910

You can use a calendar table to solve this:

SELECT date_value AS event_day, COUNT(*) AS event_count
FROM (
    SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value
    FROM
        (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
        (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
        (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
        (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
        (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) calendar INNER JOIN events ON calendar.date_value BETWEEN events.start_date AND events.end_date
WHERE calendar.date_value BETWEEN '2019-01-01' AND '2019-01-04' -- to filter for a specific date range.
GROUP BY date_value

demo on dbfiddle.uk

score 0 · Answer 2 · answered Apr 23 '19 at 06:38

If you are using postgres you can generate a calendar table using generate_series, basically you need a calendar table to be able to explode the dates.

WITH a AS(
  Select '2019-01-01'::date as start_date  ,'2019-01-04'::date as  end_date union all
  Select '2019-01-02'::date , '2019-01-03'::date union all
  Select '2019-01-01'::date, '2019-01-06'::date 
  )
 Select t.date_generated,count(*) as event
 from a
 JOIN(Select date_generated
  from generate_series(date '2019-01-01',
                   date '2019-12-31',
                   interval '1 day') as t(date_generated)
   ) t
   ON t.date_generated between a.start_date and a.end_date
   group by t.date_generated
   order by t.date_generated

M Danish · Answer 3 · 2019-04-23T06:55:05.297

0

 select Calendar.Calndr_date , count(Calendar.Calndr_date) count_events
 from event_table
 join Calendar on
 Calendar.Calndr_date between  event_table.start_date and event_table.end_date
 group by Calendar.Calndr_date

please discuss if any problem. Please create calendar table and insert data of calendar.

edited Apr 23 '19 at 06:55

answered Apr 23 '19 at 06:47

M Danish

480
2
5

Explode and Count all items from 2 dates column

3 Answers3