CODEWITHSUNDEEP
sqlsql-serverdatecasebetween
aksent1344
aksent1344

Reputation: 43

Create a column in SQL that shows if specific month is between two dates

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

Upvotes: 0

Views: 105

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272386

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;

DB<>Fiddle

Upvotes: 1

Related Questions