How can I find the status in each month using a start and end date?

Question

[ Title was: "Find out the facts: How to find the month wise active members in an healthcare organization per each year and also find the growth percentage" ]

i have 5 years of history data and would like to do some analytics on it. the data will contain active and inactive members data. the ask is for finding the active members per each month per each year.

what i am doing is am extracting month and year from effective data and grouping by month and year based on active status i.e. Status ='Active'

But in this manner I am losing the history records. for example, if a person had membership from 01-01-2015 to 31-12-2016. this member will be shown as an inactive member now but the same person was an active member in that duration. So if I filter on the status, I will lose these old records.

i need to go to that month, Jan 2015 and check all whoever were active by that time. So I thought of doing another way.

I have extracted the month of expiry date and filtered like exp_month equal to or greater than extracted month of effective date as shown below. Here, I am not relying on the incoming source field containing member status. I am creating a field with logic to identify the status of the member during the period we are finding. This is just to identify active members per each month of year But i am not sure if this is giving me the perfect solution. Please suggest me the better approach.

    SELECT extract(YEAR FROM member_effective_date)   AS year
         , extract(MONTH FROM member_expiry_date)     AS month
         , CASE WHEN extract(MONTH FROM member_expiry_date) 
                     = extract(MONTH FROM member_effective_date) 
                  OR extract(MONTH FROM member_expiry_date) 
                     > extract(MONTH FROM member_effective_date) 
                THEN 'Yes'
           ELSE 'No' END                              AS active_status
    FROM table_name

Joe Harris Joe Harris · Accepted Answer · 2020-08-13T17:31:01

You need to use a cross join with table of dates to get the status in each period. The cross join "inflates" the status table so you can evaluate the status for each period.

Here is an example:

    CREATE TEMP TABLE table_name AS
    SELECT 'member1'          AS member
         , '2020-01-01'::DATE AS member_effective_date
         , '2020-04-27'::DATE AS member_expiry_date
    ;

    WITH month_list 
        -- Month start and end for previous 12 months
    AS (SELECT DATE_TRUNC('month',dt) AS month_start
             , MAX(dt)                AS month_end
        FROM 
             -- List of the previous 365 dates
             (SELECT DATE_TRUNC('day',SYSDATE)  - (n * INTERVAL '1 day') AS dt
              FROM 
                   -- List of numbers from 1 to 365
                   (SELECT ROW_NUMBER() OVER () AS n FROM stl_scan LIMIT 365) )
        GROUP BY month_start
    )
    SELECT extract(YEAR  FROM b.month_start) AS year
         , extract(MONTH FROM b.month_start) AS month
         , CASE WHEN -- Effective before the month ended and 
                     (a.member_effective_date <= b.month_end
                 AND  a.member_expiry_date > b.month_start) 
                THEN 'Yes'
           ELSE 'No' END                              AS active
    FROM table_name a
    CROSS JOIN month_list b -- Explicit cartesian product
    ORDER BY 1,2
    ;

| year | month | active|
|------|-------|-------|
| 2019 |     8 | No    |
| 2019 |     9 | No    |
| 2019 |    10 | No    |
| 2019 |    11 | No    |
| 2019 |    12 | No    |
| 2020 |     1 | Yes   |
| 2020 |     2 | Yes   |
| 2020 |     3 | Yes   |
| 2020 |     4 | Yes   |
| 2020 |     5 | No    |
| 2020 |     6 | No    |
| 2020 |     7 | No    |
| 2020 |     8 | No    |

How can I find the status in each month using a start and end date?

1 Answers