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

This is my use case:

Table: result

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

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

CodePudding user response：

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.