How to get all the dates between the date range (inclusive start_date and end_date) [closed]

How to get all the dates between start_date and end_date, including start_date and end_date?

But not those dates that do not fall in the range.

eg: 08-01-2020 should not be in the output because it is not in any of the work_id dates range so 08-01-2020 is one of the non-working dates

So I want all the dates except the non-working dates.

Sample table :

Sample output : either this

or this

Answer

Create a list of dates of target range, add working and non-working flags to the list, and filter by the flag. You can get the expected output with the following query.

-- 1st, generate a list of dates
with dates as (
select min(start_date) as dates, max(end_date) as max_date from dates_range
union all
select dateadd(day, 1, dates), max_date from dates
where dates < max_date
),
-- 2nd, generate a list of dates with working/non working flag
all_dates as (
select distinct
  d1.dates,
  case when d2.work_id is not null then 'true' else 'false' end as working
  from dates as d1 left outer join dates_range as d2
  on d1.dates between d2.start_date and d2.end_date
)
-- 3rd, filter either that working is true or false
select dates as Non_working_dates from all_dates where working = 'false';

db<>fiddle