Split up by specific time period (Such as: split up data by last 2-3 months ...4-5 months, 6-7 month-CodePudding

I have a simple problem, I do not know where I am doing wrong. I really appreciate your help. Thanks in advance for any hint.

I have data and need to split the release date up by within 1 month, 2-3 months, 4-5 months, 6-7 months, 7-9 months, and so on. So, I have created a query ;

Select    case
          when release_date >= current_date - interval '1 month' then 'Within 1 month'
          when release_date between (current_date - interval '2 months') and (current_date - interval '3 months') then '2-3 months'
          when release_date between (current_date - interval '4 months') and (current_date - interval '5 months') then '4-5 months'
          when release_date between (current_date - interval '6 months') and (current_date - interval '7 months') then '6-7 months'
          when release_date between (current_date - interval '8 months') and (current_date - interval '9 months') then '8-9 months'
          when release_date between (current_date - interval '10 months') and (current_date - interval '12 months') then '9-12 months'
          when release_date < current_date - interval '12 months' then '> 12 month'
          end as release_date,
          country,
          ....

at the end I can only see within 1 month, NULL and > 12 month.

CodePudding user response：

If I'm reading your query correctly, you need to swap the values in your "between" statements:

Select    case
          when release_date >= current_date - interval '1 month' then 'Within 1 month'
          when release_date between (current_date - interval '3 months') and (current_date - interval '2 months') then '2-3 months'
          when release_date between (current_date - interval '5 months') and (current_date - interval '4 months') then '4-5 months'
          when release_date between (current_date - interval '7 months') and (current_date - interval '6 months') then '6-7 months'
          when release_date between (current_date - interval '9 months') and (current_date - interval '8 months') then '8-9 months'
          when release_date between (current_date - interval '12 months') and (current_date - interval '9 months') then '9-12 months'
          when release_date < current_date - interval '12 months' then '> 12 month'
          end as release_date,
          country,

I do not have a system to run this on to confirm, but it should work.

The issue is how between works, BETWEEN (lower_limit) AND (upper_limit).

CodePudding user response：

I think your output is generating a calculation other than "months". The answers depend on which dbms you are using. This is one way to perform in postgres, but if you are using another dbms, then you should be able to easily change the syntax for calculating months in the CTE. Using the CTE enables you to have a clean case statement.

    with calculated_months as
    (
      select release_date,  
      extract(year from age(current_date, release_date)) * 12
         extract(month from age(current_date, release_date))
         1   as months_from_current
    from t1
    )
    select t.release_date, 
    case
        when c.months_from_current < 2 then 'Within 1 month'
        when c.months_from_current between 2 and 3 then '2-3 months'
        when c.months_from_current between 4 and 5 then '4-5 months'
        when c.months_from_current between 6 and 7 then '6-7 months'
        when c.months_from_current between 8 and 9 then '8-9 months'
        when c.months_from_current between 10 and 12 then '10-12 months'
        when c.months_from_current > 12 then '12  months'
    end months_since_release,
    t.country
    from t1 t
    join calculated_months c
      on c.release_date = t.release_date

db-fiddle here

EDIT: Not sure how your data appear, but you may want to add a "distinct" in the CTE (select distinct release date...).