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

26.05.15

Разработка - Запросы

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

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

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

СсылкаДата возникновения долгаКол-во дней отсрочки
Док 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.ДатаКалендаря

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

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

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

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

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

банковские рабочие число дней в запросе срез последних дни запрос

См. также

SALE! 20%

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

13000 10400 руб.

02.09.2020    121633    670    389    

712

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5740    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2000    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6282    31    mkalimulin    25    

49

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1742    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5386    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16168    skovpin_sa    14    

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

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

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

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

P.S. В нашем случае дата возникновения долга по разным условиям договора может меняться (долг считается от каждой отгрузки, от полной отгрузки, от определенной даты, от даты подписания документов и др.) .
7. karpik666 3760 27.05.15 11:24 Сейчас в теме
(6) можно поставить отбор на календарь по минимуму из даты реализаций, чтобы не получать записи ненужного периода. Так-то запрос еще дольше будет работать =) И еще остается вопрос с реализациями на конец года, так как производственный календарь утверждают где-то в декабре, то и получается, что с такими реализациями будет проблема, так как на момент проверки записей производственного календаря еще не будет.
9. cj512 28 27.05.15 12:23 Сейчас в теме
(7) karpik666, как вариант можно заполнить производственный календарь по тем праздникам и выходным, по которым есть информация. Как только правительство скажет что куда переносится, то так и сделаем.

(8) ildarovich, объективно, индексы нужны.
8. ildarovich 7850 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 Ответить
10. pvlunegov 157 09.06.15 10:41 Сейчас в теме
Просьба указать для каких конфигураций подходит запрос.
В УТ 10.3 точно подходит, есть регистр нужный.
Подходил ли для УПП, БП?
11. cj512 28 18.06.15 11:21 Сейчас в теме
(10) pvlunegov, это пример реализации в расчете банковских дней от какой-то даты в запросе.
Подходит для УПП и БП (вроде там тоже есть такой регистр), в других конфигурациях, если есть подобный регистр, то можно использовать.
12. gaabora 46 20.06.15 20:54 Сейчас в теме
А на сколько лет вперед\назад обычно заполнены регистры, содержащие данные календаря в околотиповых конфигурациях?
13. kapustinag 13.05.16 07:39 Сейчас в теме
Запрос (0) не совсем правильный, кстати. Если документ реализации был в выходной или праздничный день, индекс этого дня (ИндексДатыДолга) отсутствует, и, следовательно, не определяется дата возникновения просроченного долга.

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

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

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

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

Всё.

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

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