Taking this column of dates and grouping them 1-30, 31- 59, 60 - 89, 90, both upcoming and those da-CodePudding

I have a dates column that I am trying to group in to specific groups. Some dates have passed other dates are in the future. I want to have group them by 1-30, 31- 59, 60 - 89, 90, so there would be 8 groups total for both those that have passed and those that are on the horizon. Below is what I have wrote so far, but I feel like I am over complicating and the more tweaks I make the more incorrect it becomes. Any insight is appreciated!

CASE WHEN DATEDIFF(CURRENT_DATE(),`date`) >= 90 THEN '90 days past'
     WHEN DATEDIFF(CURRENT_DATE(),`date`) >= 60 THEN '60 days past'
     WHEN DATEDIFF(CURRENT_DATE(),`date`) >= 30 THEN '30 days past'
     WHEN DATEDIFF(`date`,CURRENT_DATE()) >= 90 THEN '90 days future'
     WHEN DATEDIFF(`date`,CURRENT_DATE()) >= 60 THEN '60 days future'
     WHEN DATEDIFF(`date`,CURRENT_DATE()) >= 30 THEN 'Next 30 days'
     ELSE 'Not Late'
END

CodePudding user response：

I think it's okay.

I would just use the same datediff for each.

create table test (`date` date);
insert into test values
( date_add(current_date, interval -90 day) ), 
( date_add(current_date, interval -89 day) ), 
( date_add(current_date, interval -60 day) ), 
( date_add(current_date, interval -59 day) ), 
( date_add(current_date, interval -30 day) ), 
( date_add(current_date, interval -29 day) ),
( date_add(current_date, interval 29 day) ),
( date_add(current_date, interval 30 day) ), 
( date_add(current_date, interval 59 day) ), 
( date_add(current_date, interval 60 day) ), 
( date_add(current_date, interval 89 day) ), 
( date_add(current_date, interval 90 day) )

select `date`, 
 CASE
 WHEN DATEDIFF(`date`, CURRENT_DATE) <= -90 THEN '90 days past'
 WHEN DATEDIFF(`date`, CURRENT_DATE) <= -60 THEN '60 days past'
 WHEN DATEDIFF(`date`, CURRENT_DATE) <= -30 THEN '30 days past'
 WHEN DATEDIFF(`date`, CURRENT_DATE) >=  90 THEN '90 days future'
 WHEN DATEDIFF(`date`, CURRENT_DATE) >=  60 THEN '60 days future'
 WHEN DATEDIFF(`date`, CURRENT_DATE) >=  30 THEN 'Next 30 days'
 ELSE 'Not Late'
 END as status
from test

date       | status        
:--------- | :-------------
2021-10-12 | 90 days past  
2021-10-13 | 60 days past  
2021-11-11 | 60 days past  
2021-11-12 | 30 days past  
2021-12-11 | 30 days past  
2021-12-12 | Not Late      
2022-02-08 | Not Late      
2022-02-09 | Next 30 days  
2022-03-10 | Next 30 days  
2022-03-11 | 60 days future
2022-04-09 | 60 days future
2022-04-10 | 90 days future

db<>fiddle here