Oracle: counting sum of flags between two dates-CodePudding

I have two tables: The first one contains the beginning and the end of an interval:

------------------------------
DATE_START   |   DATE_END
------------------------------
2020-01-04       2023-07-04
------------------------------

The second one is the calendar with dates and boolean "special day" indicator:

------------------------------
DATE         |   SPECIAL_DAY_FLAG
------------------------------
2020-01-04       1
------------------------------
2020-01-05       0
------------------------------

So I need a query, that returns only rows from the first table, where number of "special days" between DATE_START and DATE_END is greater than 14.

CodePudding user response：

You can join to your second table (which I've called 'calendar') based on the date range from the first table (which I've called 'intervals'); filter those joined records on the flag value, and count how many matches there are; and finally use having to check the count is 14 or more:

select i.date_start, i.date_end,
  count(c.calendar_date) as special_days
from intervals i
join calendar c on c.calendar_date >= i.date_start and c.calendar_date <= i.date_end
where c.special_day_flag = 1
group by i.date_start, i.date_end
having count(c.calendar_date) >= 14

fiddle

... where I've assumed the interval is inclusive - if it isn't then change to < end_date.

If you wanted to count total matched days as well as special days you could also use conditional aggregation; which would need to be in a subquery to then filter by number of special days.