gifts2017

Как прибавить разное количество банковских/рабочих дней в запросе на каждую дату возникновения долга

Опубликовал Евгений Игронов (cj512) в раздел Программирование - Практика программирования

В компании возникла необходимость в получении даты возникновения просрочки по условиям договора либо в календарных, либо в рабочих днях. С календарными днями все просто. Здесь рассмотрим получение даты просрочки в рабочих днях.

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

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

СсылкаДата возникновения долгаКол-во дней отсрочки
Док 1 04.03.2015 90
Док 2 04.03.2015  5
Док 3 04.03.2015 10
Док 4 05.03.2015  60

 В результате должны получить таблицу вида

СсылкаДата возникновения долгаКол-во дней отсрочкиДата возникновения просроченного долга
Док 1 04.03.2015 90  
Док 2 04.03.2015 5  
Док 3 04.03.2015 10  
Док 4 05.03.2015 60  

 

Первоначально был сделан отчет на СКД, куда помещался внешний набор данных. Но, посидев-покурив, решили сварганить запрос. И вот о нем и расскажу вам.

Реализация:

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

ВЫБРАТЬ 
	РеализацияТоваровУслуг.Ссылка,
	НАЧАЛОПЕРИОДА(РеализацияТоваровУслуг.Дата, ДЕНЬ) КАК Дата,
	РеализацияТоваровУслуг.ДоговорКонтрагента.ДопустимоеЧислоДнейЗадолженности КАК ДнейОтсрочкиДолга
ПОМЕСТИТЬ ВТ
ИЗ
	Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг

Само собой получим Регламентированный производственный календарь.

ВЫБРАТЬ
	РегламентированныйПроизводственныйКалендарь.ДатаКалендаря
ПОМЕСТИТЬ ВТ_РеглКалендарь
ИЗ
	РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
	РегламентированныйПроизводственныйКалендарь.ВидДня В(&ВидДня)

Здесь необходимо установить параметр "ВидДня" (значения перечисления Рабочий и Предпраздничный). Далее соединяем таблицу календаря саму с собой и получаем индекс даты (т.е. порядок следования записей) в нашей таблице

ВЫБРАТЬ
	ВТ_РеглКалендарь.ДатаКалендаря,
	КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_РеглКалендарь1.ДатаКалендаря) КАК ИндексРегламентированногоКалендаря
ПОМЕСТИТЬ ВТ_РеглКалендарьСИндексами
ИЗ
	ВТ_РеглКалендарь КАК ВТ_РеглКалендарь
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_РеглКалендарь КАК ВТ_РеглКалендарь1
		ПО ВТ_РеглКалендарь.ДатаКалендаря >= ВТ_РеглКалендарь1.ДатаКалендаря

СГРУППИРОВАТЬ ПО
	ВТ_РеглКалендарь.ДатаКалендаря
ИНДЕКСИРОВАТЬ ПО
        ИндексРегламентированногоКалендаря

Тут соединим таблицу ВТ и ВТ_РеглКалендарьСИндексами для того, что бы узнать какой индекс будет у нашей даты возникновения долга. Дополнительно добавим индексацию по расчетному полю для дальнейших соединений.

ВЫБРАТЬ
	ВТ.Ссылка,
	ВТ.Дата,
	ВТ.ДнейОтсрочкиДолга,
	ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря КАК ИндексДатыДолга
ПОМЕСТИТЬ ВТ_СИндексом
ИЗ
	ВТ КАК ВТ
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеглКалендарьСИндексами КАК ВТ_РеглКалендарьСИндексами
		ПО ВТ.Дата = ВТ_РеглКалендарьСИндексами.ДатаКалендаря

Здесь приведу результаты промежуточной таблицы для наглядности.

СсылкаДата возникновения долгаКол-во дней отсрочкиИндекс даты долга
Док 1 04.03.2015 90 1 293
Док 2 04.03.2015 5 1 293
Док 3 04.03.2015 10 1 293
Док 4 05.03.2015 60 1 294

Здесь колонка "Индекс даты долга" показывает индекс поля "Дата возникновения долга" в таблице ВТ_РеглКалендарьСИндексами.

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

ВЫБРАТЬ
	ВТ_СИндексом.Ссылка,
	ВТ_СИндексом.Дата КАК ДатаВозникновенияДолга,
	ВТ_СИндексом.ДнейОтсрочкиДолга,
	ВТ_РеглКалендарьСИндексами.ДатаКалендаря КАК ДатаВозникновенияПросроченногоДолга
ИЗ
	ВТ_СИндексом КАК ВТ_СИндексом
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеглКалендарьСИндексами КАК ВТ_РеглКалендарьСИндексами
		ПО (ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря - ВТ_СИндексом.ИндексДатыДолга = ВТ_СИндексом.ДнейОтсрочкиДолга)

Здесь условие соединения означает, что мы хотим получить только ту дату из таблицы ВТ_РеглКалендарьСИндексами, у которой  разница Индекса регламентированного календаря и Индекса даты долга будет равно именно количеству дней отсрочки.

Результирующая таблица выглядит следующим образом:

СсылкаДата возникновения долгаКол-во дней отсрочкиДата возникновения просроченного долга
Док 1 04.03.2015 90 14.07.2015
Док 2 04.03.2015 5 12.03.2015
Док 3 04.03.2015 10 19.03.2015
Док 4 05.03.2015 60

02.06.2015

 

Результирующий запрос

ВЫБРАТЬ
	РеализацияТоваровУслуг.Ссылка,
	НАЧАЛОПЕРИОДА(РеализацияТоваровУслуг.Дата, ДЕНЬ) КАК Дата,
	РеализацияТоваровУслуг.ДоговорКонтрагента.ДопустимоеЧислоДнейЗадолженности КАК ДнейОтсрочкиДолга
ПОМЕСТИТЬ ВТ
ИЗ
	Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	РегламентированныйПроизводственныйКалендарь.ДатаКалендаря
ПОМЕСТИТЬ ВТ_РеглКалендарь
ИЗ
	РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
	РегламентированныйПроизводственныйКалендарь.ВидДня В(&ВидДня)
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТ_РеглКалендарь.ДатаКалендаря,
	КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_РеглКалендарь1.ДатаКалендаря) КАК ИндексРегламентированногоКалендаря
ПОМЕСТИТЬ ВТ_РеглКалендарьСИндексами
ИЗ
	ВТ_РеглКалендарь КАК ВТ_РеглКалендарь
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_РеглКалендарь КАК ВТ_РеглКалендарь1
		ПО ВТ_РеглКалендарь.ДатаКалендаря >= ВТ_РеглКалендарь1.ДатаКалендаря

СГРУППИРОВАТЬ ПО
	ВТ_РеглКалендарь.ДатаКалендаря
ИНДЕКСИРОВАТЬ ПО
        ИндексРегламентированногоКалендаря
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТ.Ссылка,
	ВТ.Дата,
	ВТ.ДнейОтсрочкиДолга,
	ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря КАК ИндексДатыДолга
ПОМЕСТИТЬ ВТ_СИндексом
ИЗ
	ВТ КАК ВТ
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеглКалендарьСИндексами КАК ВТ_РеглКалендарьСИндексами
		ПО ВТ.Дата = ВТ_РеглКалендарьСИндексами.ДатаКалендаря
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТ_СИндексом.Ссылка,
	ВТ_СИндексом.Дата КАК ДатаВозникновенияДолга,
        ВТ_СИндексом.ДнейОтсрочкиДолга,
	ВТ_РеглКалендарьСИндексами.ДатаКалендаря КАК ДатаВозникновенияПросроченногоДолга
ИЗ
	ВТ_СИндексом КАК ВТ_СИндексом
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеглКалендарьСИндексами КАК ВТ_РеглКалендарьСИндексами
		ПО (ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря - ВТ_СИндексом.ИндексДатыДолга = ВТ_СИндексом.ДнейОтсрочкиДолга)

Ссылки на другие публикации подобной темы:

http://infostart.ru/public/99507/ Банковские дни запросом 
http://infostart.ru/public/166349/ Добавить к дате рабочие дни в запросе 
http://infostart.ru/public/320887/ Расчет рабочих дней в запросе 
http://infostart.ru/public/338386/ Расчет банковских (рабочих) дней (Оригинальный способ) 
http://infostart.ru/public/358354/ Прибавление банковских дней к дате в запросе 

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Марина Чирина (chmv) 27.05.15 09:48
2. Андрей Карпов (karpik666) 27.05.15 10:03
А что за конфигурацию вы используете, где есть Допустимое число дней задолженности?
3. Евгений Игронов (cj512) 27.05.15 10:20
(2) karpik666, это типовой реквизит справочника "Договоры контрагентов" в УПП, нами же был добавлен реквизит "Контроль оплаты в рабочих днях" (тип булево).

Думаю в бухгалтерии такое значение может выполнять доп. свойство договора.
4. Андрей Карпов (karpik666) 27.05.15 10:37
(3) cj512, а сколько по времени работает запрос, к сожалению у себя не могу протестировать, как вижу здесь вообще не стоит отбор на календарь, да и на реализации. А что если реализация будет сделана в выходной день? тогда у вас запрос вернет null. И левое соединение никак не обрабатывается, тогда уж лучше сделать внутреннее. И еще один нюанс, если дата реализации 14.10.2015 и кол-во дней просрочки 90 дней, то какую он дату покажет как просрочку?
5. Евгений Игронов (cj512) 27.05.15 11:07
(4) karpik666, хорошее замечание. Здесь я описал возможный вариант реализации.

1. Отбор на календарь не стоит, т.к. в нашем случае актуальный долг мог возникнуть несколько лет назад. В замере производительности запрос на календарь и соединение временной таблицы друг с другом занимает 5-7 мс.
2. Если реализации выпадает на рабочий день, то можно немного подправить запрос в части соединения.
Т.е. в этом случае мы выбираем следующий рабочий день от даты реализации. Внутреннее соединение не есть хорошо, т.к. лучше увидеть неверно рассчитанную дату возникновения просрочки, чем не увидеть её вовсе.
ВЫБРАТЬ
	ВТ.Ссылка,
	ВТ.Дата,
	ВТ.ДнейОтсрочкиДолга КАК ДнейОтсрочкиДолга,
	МИНИМУМ(ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря) КАК ИндексДатыДолга
ПОМЕСТИТЬ ВТ_СИндексом
ИЗ
	ВТ КАК ВТ
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеглКалендарьСИндексами КАК ВТ_РеглКалендарьСИндексами
		ПО ВТ.Дата <= ВТ_РеглКалендарьСИндексами.ДатаКалендаря

СГРУППИРОВАТЬ ПО
	ВТ.Ссылка,
	ВТ.Дата,
	ВТ.ДнейОтсрочкиДолга
...Показать Скрыть
6. Евгений Игронов (cj512) 27.05.15 11:11
(4) karpik666, но в любом случае, первая таблица к документам реализации не означает получение задолженности. Здесь это всего лишь пример. Как вы получите первоначальную таблицу зависит от вас.

P.S. В нашем случае дата возникновения долга по разным условиям договора может меняться (долг считается от каждой отгрузки, от полной отгрузки, от определенной даты, от даты подписания документов и др.) .
7. Андрей Карпов (karpik666) 27.05.15 11:24
(6) cj512, можно поставить отбор на календарь по минимуму из даты реализаций, чтобы не получать записи ненужного периода. Так-то запрос еще дольше будет работать =) И еще остается вопрос с реализациями на конец года, так как производственный календарь утверждают где-то в декабре, то и получается, что с такими реализациями будет проблема, так как на момент проверки записей производственного календаря еще не будет.
8. Сергей (ildarovich) 27.05.15 11:33
Это всего один из возможных способов. Довольно экономный, кстати, с точки зрения расхода ресурсов. "Нарастающий итог" для расчета порядкового номера рабочего дня считается один раз, а затем многократно используется. Хорошо бы индексы еще к таблице порядковых номеров рабочих дней построить. И можно было бы соединять не с рабочими днями, а с выходными, которых меньше. Или вообще с исключениями (праздниками и переносами).
Для полного совершенства публикации не хватает ссылок на другие публикации по той же теме.
http://infostart.ru/public/99507/ Банковские дни запросом
http://infostart.ru/public/166349/ Добавить к дате рабочие дни в запросе
http://infostart.ru/public/320887/ Расчет рабочих дней в запросе <<<- Здесь ровно тот же способ!!!
http://infostart.ru/public/338386/ Расчет банковских (рабочих) дней (Оригинальный способ)
http://infostart.ru/public/358354/ Прибавление банковских дней к дате в запросе
Вообще приведение ссылок на другие публикации той же темы - правило хорошего тона, использование которого приносит и автору и читателям много пользы, дает "кумулятивный" эффект. - Не сделать ли этот блок в публикации обязательным?
Redokov; karpik666; +2 Ответить 1
9. Евгений Игронов (cj512) 27.05.15 12:23
(7) karpik666, как вариант можно заполнить производственный календарь по тем праздникам и выходным, по которым есть информация. Как только правительство скажет что куда переносится, то так и сделаем.

(8) ildarovich, объективно, индексы нужны.
10. Петр Лунегов (pvlunegov) 09.06.15 10:41
Просьба указать для каких конфигураций подходит запрос.
В УТ 10.3 точно подходит, есть регистр нужный.
Подходил ли для УПП, БП?
11. Евгений Игронов (cj512) 18.06.15 11:21
(10) pvlunegov, это пример реализации в расчете банковских дней от какой-то даты в запросе.
Подходит для УПП и БП (вроде там тоже есть такой регистр), в других конфигурациях, если есть подобный регистр, то можно использовать.
12. gaabora@gmail.com (gaabora) 20.06.15 20:54
А на сколько лет вперед\назад обычно заполнены регистры, содержащие данные календаря в околотиповых конфигурациях?
13. Александр Капустин (kapustinag) 13.05.16 07:39
Запрос (0) не совсем правильный, кстати. Если документ реализации был в выходной или праздничный день, индекс этого дня (ИндексДатыДолга) отсутствует, и, следовательно, не определяется дата возникновения просроченного долга.

Исправления этого могут быть различные, могу дать свой работающий вариант, если это еще кому-то интересно.
14. Евгений Игронов (cj512) 13.05.16 17:33
(13) kapustinag, почему бы и нет. Внесу исправление в статью от вашего имени.
15. Александр Капустин (kapustinag) 13.05.16 20:04
(14) cj512, После формирования временной таблицы ВТ_РеглКалендарьСИндексами я добавил формирование еще одной временной таблицы;

ВЫБРАТЬ
ВТ_РеглКалендарьСИндексами.ДатаКалендаря,
ЕстьNULL(Таб.ДатаКалендаря,КОНЕЦПЕРИОДА(ВТ_РеглКалендарьСИндексами.ДатаКалендаря,ДЕНЬ)) КАК СледующаяРабочаяДата,
ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря
ПОМЕСТИТЬ ВТ_РеглКалендарьСИндексами2
ИЗ ВТ_РеглКалендарьСИндексами КАК ВТ_РеглКалендарьСИндексами
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеглКалендарьСИндексами КАК Таб
ПО ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря = Таб.ИндексРегламентированногоКалендаря-1
;

Таким образом, строки этой временной таблицы содержат, кроме даты строки, дату следующего рабочего дня.
После этого запрос, формирующий временную таблицу ВТ_СИндексом, меняем вот таким образом:

ВЫБРАТЬ
ВТ.Ссылка,
ВТ.Дата,
ВТ.ДнейОтсрочкиДолга,
ВТ_РеглКалендарьСИндексами.ИндексРегламентированногоКалендаря КАК ИндексДатыДолга
ПОМЕСТИТЬ ВТ_СИндексом
ИЗ
ВТ КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеглКалендарьСИндексами2 КАК ВТ_РеглКалендарьСИндексами
ПО ВТ.Дата >= ВТ_РеглКалендарьСИндексами.ДатаКалендаря И
ВТ.Дата < ВТ_РеглКалендарьСИндексами.СледующаяРабочаяДата
;

Всё.

При формировании поля "следующая рабочая дата" использую конструкцию ЕстьNULL(...) - для того чтобы в последней строке календаря это поле тоже было заполнено.

Особого падения производительности не заметил. Но и точных замеров не делал.
Может быть, пойдет и вариант, когда вместо дополнительной временной таблицы получаем дату следующего рабочего дня сразу при формировании ВТ_РеглКалендарьСИндексами - ценой еще одного внутреннего соединения.