[SOLVED] Counting sum of flags between two dates

Counting sum of flags between two dates

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.

Solution

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.