I have a table called CASE that has columns: ID, THEATER, CREATED_DATE, CLOSED_DATE
The column ID is the unique key in this table and the column THEATER has repeating values. (meaning: there could be multiple ID's for one THEATER).
There is another table called HOLIDAYS that has columns THEATER, HOLIDAY_DATE.
This table has all holiday dates listed for each THEATER.
The join between CASE and HOLIDAYS is on THEATER, but it is many:many relationship.
Now, for each CASE.ID I need to know the number of Holidays between the CREATED_DATE AND CLOSED_DATE values.
I need to achieve this in a sql query.