Есть таблица услуг: SERVICES (SERVICE_ID, LOGIN, START_DATE, STOP_DATE). START_DATE и STOP_DATE задают период действия услуги, могут быть не заданы (в этом случае период действия не ограничен).
У таблицы есть подчиненная таблица с историей тарифов: SERVICE_TARIFF (SERVICE_ID, TARIFF_ID, DATE_BEG, DATE_END).
И еще у таблицы есть подчиненная таблица с историей состояний (статусов): SERVICE_STATUS (SERVICE_ID, STATUS_ID, DATE_BEG, DATE_END). Статус -20 соответствует удаленной записи (такой статус бывает только один в истории и он всегда последний), статус -1 соответствует отключенной услуге, статус 1 соответствует подключенной услуге.

Я хочу в идеале получить такие данные:

SERVICES
SERVICE_IDLOGINSTART_DATESTOP_DATE1Услуга 12015-01-01-2Услуга 22015-01-012015-02-01

SERVICE_TARIFF
SERVICE_IDTARIFF_IDDATE_BEGDATE_END112015-01-012015-03-01122015-03-012015-04-01132015-04-01-212015-01-01-

SERVICE_STATUS
SERVICE_IDSTATUS_IDDATE_BEGDATE_END112015-01-012015-02-201-12015-02-202015-03-01112015-03-012015-04-051-202015-04-05-212015-01-012015-02-012-12015-02-01-


На небольших объемах можно было бы сгенерировать календарь, на каждый день получить тариф и статус, а затем агрегировать периоды. Но во-первых данных у меня довольно много, а во-вторых даты это не DATE, а DATETIME и теоретически в течение дня по меньшей мере статусы могут меняться более одного раза.
Вместо ежедневного календаря можно было бы получить список всех distinct-значений DATE_BEG обоих исторических таблиц, но все-равно остается «во-первых» — объем данных будет большой.

Другим решением было бы двумя разными запросами отдельно посчитать периоды тарифов и периоды активности/неактивности, объединив затем результаты на клиенте.
Но в клиенте это делать не очень удобно, хотелось бы получить результат в одном запросе.

В крайнем случае длительность активности/неактивности не столь важна и ее можно вообще пропустить, то есть получать периоды только для тарифов. Но в этом случае мне нужно как-то получить дату удаления услуги. Как это лучше сделать, отдельный left join с дополнительным условием соединения STATUS_ID=-20 или есть более правильный способ?

Дырок в исторических данных нет, все диапазоны смежные.
Но диапазоны статусов и тарифов не совпадают.
В БД DATE_END изначально определено как null и пустое значение означает, что граница не задана.
Ранее я использовал выражение nvl(DATE_END,DATE'9999-01-01'), но данных довольно много и выражение (DATE_END is null or DATE_END > sysdate) гораздо эффективнее, т.к. в этом случае используются индексы.

Сейчас я сделал по последнему варианту — учитываю только историю тарифов, историю статусов игнорирую, а дату удаления услуги получаю отдельным джойном. В принципе работает удовлетворительно.
Но буду благодарен, если подскажите более эффективный способ на будущее.

Staxи всегда 1,-1... (-20)?
Нет, есть и другие статусы, то мне нужно различать только три статуса: -20, положительный и отрицательный.

START_DATE и STOP_DATE имеют приоритет перед задаваемыми статусами.
Они задают срок действия услуги, при наступлении STOP_DATE система автоматически добавляет статус -10 в историю (то есть не удаляет услугу, а отключает ее).
В приведенном примере первая услуга была удалена, а вторая отключена по окончанию срока действия.