I want to add a column to the results, for every month. The column should contain true if it is between two dates (dates come from the same row).
For example I have date from = 2023-01-01 and date to = 2023-05-31
So I want that 2023-02 column would get value = 1 because this month is between those dates
But column 2023-06 would get value = 0, because this month is out of range.
Now I have table
Customer nr | Date from | Date to |
---|---|---|
xxxxxx | 2023-01-01 | 2023-05-31 |
yyyyyy | 2023-03-01 | 2023-10-31 |
qqqqqq | 2023-08-01 | 2023-12-31 |
But I want to have:
Customer nr | Date from | Date to | 2023.01 | 2023.02 | 2023.03 |
---|---|---|---|---|---|
xxxxxx | 2023-01-01 | 2023-05-31 | 1 | 1 | 1 |
yyyyyy | 2023-03-01 | 2023-10-31 | 0 | 0 | 1 |
qqqqq | 2023-08-01 | 2023-12-31 | 0 | 0 | 0 |
If the date_from
is always first day of month and date_to
is always last date of month then use a simple between
check:
select *
, case when '2023-01-01' between date_from and date_to then 1 else 0 end as [2023-01]
, case when '2023-02-01' between date_from and date_to then 1 else 0 end as [2023-02]
, case when '2023-03-01' between date_from and date_to then 1 else 0 end as [2023-03]
from t;
Otherwise use range overlap check (some part of the month intersects some part of the [date_from, date_to] range):
select *
, case when date_to >= '2023-01-01' AND date_from <= eomonth('2023-01-01') then 1 else 0 end as [2023-01]
, case when date_to >= '2023-02-01' AND date_from <= eomonth('2023-02-01') then 1 else 0 end as [2023-02]
, case when date_to >= '2023-03-01' AND date_from <= eomonth('2023-03-01') then 1 else 0 end as [2023-03]
from t;