I am trying to create a column called cycle grouping which will categorize based on created_at_date column, starting 9/11. I want to categorize first 14 days as first cycle and next 14 days as second cycle, and then reset it. For example:
| created_at_date | cycle_grouping (want to create this) |
| :--------------:| :------------------------------------:|
| 09/11/2022 | 09/11/2022First Cycle |
| 09/18/2022 | 09/18/2022First Cycle |
| 09/25/2022 | 09/25/2022Second Cycle |
| 10/02/2022 | 10/02/2022Second Cycle |
| 10/09/2022 | 10/09/2022First Cycle |
| 10/16/2022 | 10/16/2022First Cycle |
| 10/23/2022 | 10/23/2022Second Cycle |
| 10/30/2022 | 10/30/2022Second Cycle |
...... continue this for other weeks
Any ideas will be really appreciated.
CodePudding user response:
You can get the difference in days between 9/11/2022 and created_at_date, take the remainder after dividing by 28, and seeing if that is < 14 or not.
select created_at_date,
case when DATEDIFF(day, '9/11/2022', created_at_date) % 28 < 14 then 'First Cycle' else 'Second Cycle' end as cycle_grouping
from mytable
Note: It's <14 instead of <=14 because the first cycle goes from days 0 - 13 and the next from days 14 - 27. Day 28 starts first cycle again. You'll have to tweak slightly if that's not exactly what you want.
Update: To add the original date to that field, you can do this:
select created_at_date,
concat(created_at_date, case when DATEDIFF(day, '9/11/2022', created_at_date) % 28 < 14 then 'First Cycle' else 'Second Cycle' end) as cycle_grouping
from mytable
One thing to note: Depending on how your dates are stored and what flavor of SQL you're using, you might need to cast(created_at_date as string)
in the concat