Taking Count Based On Year and Month from Date Columns

Question

I want to take count based on from and to date. using from and to date I am trying to take year and month then based on month and year taking count. can someone suggest me how can i implement this.

Database : Snowflake

Answer 1

You want to do more less the solution to this other question

but here let me do all the work for you:

WITH data_table(start_date, end_date) as (
    SELECT * from values
        ('2022-01-15'::date, '2022-02-12'::date),
        ('2021-12-25'::date, '2022-03-18'::date),
        ('2022-02-25'::date, '2022-03-06'::date),
        ('2021-10-20'::date, '2022-01-07'::date)

), large_range as (
    SELECT row_number() over (order by null)-1 as rn
    FROM table(generator(ROWCOUNT => 1000))
), pre_condition as (
    SELECT 
        date_trunc('month', start_date) as month_start
        ,datediff('month', month_start, date_trunc('month', end_date)) as m
    FROM data_table
)
SELECT
    to_char(dateadd('month', r.rn, d.month_start),'MON-YY') as month_yr
    ,count(*) as count
FROM pre_condition as d
JOIN large_range as r ON r.rn <= d.m
GROUP BY 1;

MONTH_YR	COUNT
Jan-22	3
Dec-21	2
Feb-22	3
Oct-21	1
Nov-21	1
Mar-22	2