Create a flag to know if a column is in multiple intervals

I am struggling in SQL to create a flag based on an interval.

This is my use case:

Table: result

merchandising_month	client_id	start_month_12m_before	end_month_12m_before	start_month	end_month
202106	client1	201910	202009	202010	202109
202006	client1	201910	202009	202010	202109
202003	client2	201910	202009	202010	202109
202012	client3	201910	202009	202010	202109
202012	client4	201910	202009	202003	202109
202012	client4	201910	202009	202001	202109

The objective is based on the client_id, I need to flag his activity.

If merchandising_month is between start_month and end_month for a record and merchandising_month is between start_month_12m_before and end_month_12m_before, we flag it to yes, otherwise to no

merchandising_month	client_id	start_month_12m_before	end_month_12m_before	start_month	end_month	activity
202106	client1	201910	202009	202010	202109	yes
202006	client1	201910	202009	202010	202109	yes
202003	client2	201910	202009	202010	202109	no
202012	client3	201910	202009	202010	202109	no
202002	client4	201910	202009	202010	202109	yes
202104	client4	201910	202009	202010	202109	yes

I have tried to do a CASE WHEN filter but I find null values, I tried also an inner join, but without any success.

There is what I have tried:

select * ,
(
    CASE
    WHEN
    (
        (r1.merchandising_month >= r1.start_month_12m_before)
        AND (r1.merchandising_month <= r1.end_month_12m_before) 
    )
    THEN 'No'
    WHEN 
    (
        (r1.merchandising_month BETWEEN r1.start_month_12m_before AND r1.end_month_12m_before)
        AND (r2.merchandising_month BETWEEN r1.start_month AND r1.end_month)
    )
    THEN 'Yes'
    END
) AS activity_N
FROM result r1 INNER JOIN result r2 ON r1.client_id = r2.client_id

And it gives me the null values for each line not answering to Yes condition

Any help would be very appreciated

Answer

If you’re just looking to confirm that both conditions hold true over at least one of the rows for the same client:

select *,
    case when
        max(case when merchandising_month between start_month and end_month then 1 end)
            over (partition by client_id) = 1 and 
        max(case when merchandising_month between start_month_12m_before and end_month_12m_before then 1 end)
            over (partition by client_id) = 1
        then 'yes' else 'no' end as activity
from T

This would not differentiate the rows in any fashion. The conditions could both be true for the same row simultaneously or multiple times across the set.

The way this works is to look around at each row with the same client_id. For each of those the conditions are tested using a case expression that evaluates to 1 when the condition passes. The max() will collapse multiple values into a single result so that a maximum value across all rows being equal to 1 will indicate that at least one row has passed the test. The value 1 could be changed to something like 'Passed' or 'True' if preferred.