CODEWITHSUNDEEP
mysqlsql
Rami
Rami

Reputation: 45

finding the number of rows in a date range -MySQL

I have a query that result in this table:

                   id          accountid   amount subs_start   subs_end
1  0063r000013Jyz5AAC 0013r00002RTIBEAA5     0.00 2020-11-09 2021-11-09
2  0063r000013KSFaAAO 0014000001boC4CAAU     0.00 2020-11-30 2021-11-30
3  0063r000013KkhKAAS 0014000000YyVs0AAF     0.00 2020-11-30 2021-11-30
4  0063r000013KnHoAAK 0013r00002KHPoMAAX     0.00 2020-11-30 2021-11-30
5  0063r000013KvhlAAC 0011W00002Abn4vQAB     0.00 2020-12-21 2021-12-21

I want to write another query that lead to the following result:

2020-10-01  0
2020-11-01  4
2020-12-01  5
2021-01-01  5

the first column of this table should be the date range that I choose and the second column is the number of rows in the first query that are valid between subs_start and subs_end. for example, the first row shows that we have 4 account_id that their subs_start and subs_end is in Nov 2020.

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can generate the dates you want using a recursive CTE and then use a subquery for the calculation:

with recursive dates as (
      select date('2020-10-01') as dte
      union all
      select dte + interval 1 month
      from dates
      where dte < '2021-01-01'
     )
select d.dte,
       (select count(*)
        from t
        where t.sub_start <= d.dte and
              t.sum_end > d.dte
       ) as cnt
from dates d

Upvotes: 2

Related Questions