Вытеснение периодических условий по периодам (в запросе)

Существуют некоторые весьма полезные запросы, знание которых дает преимущество при реализации алгоритмов. Одним из таких запросов является «Вытеснение условий по периодам».

Постановка задачи

Допустим, организация занимается сдачей объектов недвижимости в аренду. Сущность таких условий является периодической, т.е. указывается ставка за месяц, но при этом договор имеет более длительный срок. Соответственно, такие условия удобно хранить в регистре сведений.

В процессе «жизни» между организацией и арендатором могут быть подписаны дополнительные соглашения, например, о скидке на некоторый срок в период действия договора.

Для того чтобы получить «итоговую» картину действующих условий как раз и удобен данный алгоритм.

Входящие данные

01.01.2017 с арендатором ИП Пупкин организация заключает договор аренды на срок до 31.12.2017. По условия аренды ИП Пупкин должен платить: 1000 руб./месяц за постоянную часть; 500 руб./месяц за эксплуатацию; 100 руб./месяц — маркетинговый взнос.

Через некоторое время ИП Пупкин договаривается о скидке, и 01.02.2017 заключается дополнительное соглашение, в котором указывается, что ставка постоянной части на период с 15.02.2017 по 14.03.2017 составит 500 руб.

Через некоторое время ИП Пупкин понимает, что бизнес идет плохо и не может платить так много за аренду помещения, организация идет навстречу арендатору и понижает ставки. В новом дополнительном соглашении указано что с 01.03.2017 по 30.04.2017 арендатор будет платить: 300 руб./месяц за постоянную часть; 250 руб./месяц за эксплуатацию; 50 руб./месяц — маркетинговый взнос.

Отчет по актуальным условиям аренды, заключенным с арендаторами

Сотруднику, занимающемуся арендой в организации, требуется отчет, который показал бы итоговый срез по условиям аренды.

На изображении ниже графически представлены входящие данные и то что необходимо получить:

Методика решения

Для того чтобы получить срез периодических условий, необходимо:

1. Создать «скелет» периодов для среза вида: [ПериодНачало1] — [ПериодКонец1];[ПериодНачало2] — [ПериодКонец2]…[ПериодНачалоN] — [ПериодКонецN] с учетом полей набора данных (контрагент + договор + услуга). Для этой цели каждую граничную точку периодов разбиваем на 2 даты: из даты начала, например, 01.03.2017, мы получим дату начала 01.03.2017 и дату конца 28.02.2017; для даты конца, например, 30.04.2017, получим дату начала 01.05.2017 и дату конца 30.04.2017.
2. Далее, из дат начал и дат концов получим таблицу в которой, каждой дате начала должна соответствовать ближайшая, превосходящая дату начала, дата конца. При этом записи с датами начала или конца равными NULL (не соединившимися), мы отсекаем.
3. После чего, наш «скелет» соединим с условиями и получим максимальную дату регистрации условий, которые действовали в данном периоде. Здесь сразу оговорюсь, что в данном примере считается что 2 изменения условий по одинаковому набору данных (контрагент + договор + услуга) в одну дату быть не может (что соответствует бизнес-логике). Если же такая ситуация возможна, необходимо добавить приоритет регистраторов/иных условий и добавить шаг, аналогичный этому, но для регистраторов/иных условий.
4. Зная периоды и дату регистрации условий, соединим с самими условиями и получим итоговый срез.

В результате получим таблицу, которую можно представить в следующем виде (для удобства данные выведены по группировкам):

Надеюсь, знание алгоритма данного запроса сэкономит вам ваше время.

Пример базы, а так же сам запрос можно скачать во вложении к статье.