Create a category column based on date-CodePudding

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