Aggregate monthly rows created date and ended date

Question

I need to adapt a graph from the current BI implementation to an SQL one. This graph reflects the amount of requests received and each one of these requests have 3 fields that are relevant for this query: the id, created date and the end date.

The graph looks like this https://i.stack.imgur.com/NRIjr.png:

+----+--------------+-------------+
| ID |  CREATE_DATE |   END_DATE  |
+----+--------------+-------------+
|    |              |             |
| 1  |  2022-01-01  |  2022-02-10 |
|    |              |             |
| 2  |  2022-01-03  |  2022-03-01 |
|    |              |             |
| 3  |  2022-02-01  |  2022-04-01 |
|    |              |             |
| 4  |  2022-03-01  |  null       |
+----+--------------+-------------+

So for this particular example we'd have something like this:

• January: active: 2 (requests 1 and 2), finished: 0;
• February: active 2 (requests 2, 3), finished 1 (request 1);
• March: active 2 (requests 3, 4) finished 1 (request 2)

So for each month I want the active requests for that particular month (those that their ended date goes after that particular month or is null) and the requests that finished during that month (this one might be split to another query, of course) I tried this query, but of course, it doesn't take into account the requests that ended in a particular month, and only gives me the cumulative sum

Edit: I forgot to mention that one of the requirements is that the beggining and end date of the graph might be set by the user. So maybe I want to see the months from April-2022 to April-2020 and see the 2 year behaviour!

 WITH cte AS ( SELECT
        date_trunc('month',
        r.date_init) AS mon,
        count(r.id) AS mon_sum
    FROM
        "FOLLOWUP"."CAT_REQUEST" r
    GROUP  BY
        1     )  SELECT
        to_char(mon,
        'YYYY-mm') AS mon_text,
        COALESCE(sum(c.mon_sum) 
                     OVER (ORDER BY mon),
                 0) AS running_sum
    FROM
        generate_series('2022-01-01', '2023-12-25',
        interval '1 month') mon
    LEFT   JOIN
        cte c USING (mon)
    ORDER  BY
        mon

Answer 1

I wrote query for you using some different business logic. But, result is will be same result which you needed. Sample query:

with month_list as (
    select 1 as id,  'Yanuary' as mname     union all
    select 2 as id,  'Febriary' as mname    union all
    select 3 as id,  'Marth' as mname       union all   
    select 4 as id,  'April' as mname       union all   
    select 5 as id,  'May' as mname         union all   
    select 6 as id,  'June' as mname        union all   
    select 7 as id,  'Jule' as mname        union all   
    select 8 as id,  'August' as mname      union all   
    select 9 as id,  'September' as mname   union all   
    select 10 as id, 'October' as mname     union all   
    select 11 as id, 'November' as mname    union all   
    select 12 as id, 'December' as mname
), 
test_table as (
    select 
        id, 
        create_date, 
        end_date, 
        extract(month from create_date) as month1, 
        extract(month from end_date) as month2 
    from 
        your_table
)
select 
    t1.mname, 
    count(*) as "actived"
from 
    month_list t1 
inner  join 
    test_table t2 on (t1.id >= t2.month1) and (t1.id < t2.month2)
group by 
    t1.id, t1.mname
order by 
    t1.id


/* --- Result: 

mname       actived
--------------------
 Yanuary     2
 Febriary    2
 Marth       1

*/

Answer 2

PostgreSQL has many date & time functions and types. I write some samples for you: For example, in my samples function now() our chosen date.

-- get previos 12 month from date (return timestampt)
select now() - '12 month'::interval as newdate 
-- Return:
2021-04-03 18:22:48.344 +0400

-- if you need only date, you can cast this to date 
select (now() - '12 month'::interval)::date as newdate 
-- Return:
2021-04-03


-- generate data from previous 12 month to selected date increase by month: 
SELECT t1.datelist::date 
from generate_series
     (
        now()-'12 month'::interval, 
        now(),
        '1 month'
     ) 
AS t1(datelist)
-- Return:
2021-04-03
2021-05-03
2021-06-03
2021-07-03
2021-08-03
2021-09-03
2021-10-03
2021-11-03
2021-12-03
2022-01-03
2022-02-03
2022-03-03
2022-04-03


-- generate data from previous 12 month to selected date increase by month with extracting month names and year: 
-- this sample may be as you needed.
SELECT 
    extract(year from t1.datelist) as "year", 
    TO_CHAR(t1.datelist, 'Month') as "month", 
    trim(TO_CHAR(t1.datelist, 'Month')) || '-' || trim(to_char(t1.datelist, 'yyyy')) as "formatted_date"
from generate_series
     (
        now()-'12 month'::interval, 
        now(),
        '1 month'
     ) 
AS t1(datelist)
-- Return: 

year    month       formatted_date
------------------------------------
2021    April       April-2021
2021    May         May-2021
2021    June        June-2021
2021    July        July-2021
2021    August      August-2021
2021    September   September-2021
2021    October     October-2021
2021    November    November-2021
2021    December    December-2021
2022    January     January-2022
2022    February    February-2022
2022    March       March-2022
2022    April       April-2022