Home > Back-end >  Create a flag to know if a column is in multiple intervals
Create a flag to know if a column is in multiple intervals

Time:12-21

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

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.

  •  Tags:  
  • sql
  • Related