By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (
nr varchar(10),
date_from date,
date_to date
);
insert into t (nr, date_from, date_to) values
('xxxxxx', '2023-01-01', '2023-05-31'),
('yyyyyy', '2023-03-01', '2023-10-31'),
('qqqqqq', '2023-08-01', '2023-12-31');
3 rows affected
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;
| 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 |
| qqqqqq | 2023-08-01 | 2023-12-31 | 0 | 0 | 0 |
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;
| 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 |
| qqqqqq | 2023-08-01 | 2023-12-31 | 0 | 0 | 0 |