Расчет рабочих дней в запросе

25.12.14

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

+23 –
Популярная тема - расчет просрочки в рабочих днях. Если это нужно сделать по одному объекту, то тут всё достаточно просто. Что же делать, если нужно сразу по множеству объектов, при этом быстро и не особо загружая сервер?

Исходные данные

Есть регистр сведений с данными по регламентированному производственному календарю "РегламентированныйПроизводственныйКалендарь". Измерение ДатаКалендаря и ресурс Рабочий типа булево, обозначающий рабочий день.

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

Текст запроса для получения вспомогательной таблицы приведен ниже. В нем сделана дополнительная оптимизация, т.к. по факту SQL перемножает матрицы  - было добавлено ограничение на получение данных по предыдущему и следующему году.

 

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	РегламентированныйПроизводственныйКалендарь.ДатаКалендаря КАК ДатаКалендаря
ПОМЕСТИТЬ ВТ_ДанныеКалендаря
ИЗ
	РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
	(РегламентированныйПроизводственныйКалендарь.Рабочий = ИСТИНА)
	И ГОД(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря) >= ГОД(&ТекущаяДата) - 1
	И ГОД(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря) <= ГОД(&ТекущаяДата) + 1

ИНДЕКСИРОВАТЬ ПО
	ДатаКалендаря
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗРЕШЕННЫЕ
	ВТ_ДанныеКалендаря.ДатаКалендаря КАК ДатаКалендаря,
	СУММА(1) КАК НомерРабочегоДня
ПОМЕСТИТЬ ВТ_НомераРабочихДней
ИЗ
	ВТ_ДанныеКалендаря КАК ВТ_ДанныеКалендаря
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДанныеКалендаря КАК ВТ_ДанныеКалендаряДляСуммы
		ПО ВТ_ДанныеКалендаря.ДатаКалендаря >= ВТ_ДанныеКалендаряДляСуммы.ДатаКалендаря

СГРУППИРОВАТЬ ПО
	ВТ_ДанныеКалендаря.ДатаКалендаря

ИНДЕКСИРОВАТЬ ПО
	ДатаКалендаря



рабочие дни расчет просрочки матрицы

+23 –

См. также

Infostart Toolkit — Инструменты разработчика 1С 8.3

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

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

12000 руб.

02.09.2020    169271    937    403    

905
Шпаргалка по запросам

Запросы Программист Бесплатно (free)

Увидел cheatsheet по SQL и захотелось нарисовать подобное, но про запросы.

18.10.2024    11392    sergey279    18    

65
Ловушка функции запроса АВТОНОМЕРЗАПИСИ()

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

Столкнулся с интересной ситуацией, которую хотел бы разобрать, ввиду её неочевидности. Речь пойдёт про использование функции запроса АВТОНОМЕРЗАПИСИ() и проблемы, которые могут возникнуть.

11.10.2024    6338    XilDen    36    

83
Внутренняя жизнь ваших запросов PostgreSQL. Как и зачем «подглядывать» в подробности

Запросы Программист Запросы Бесплатно (free)

Отлаживая взаимодействие с базой данных, мы регулярно сталкиваемся с зависающими или подозрительно долго выполняющимися обращениями, негативно влияющими на производительность. О том, как в PostgreSQL выявить подозрительные запросы, основываясь на доступной о них информации, расскажем в статье.

16.08.2024    9068    user1840182    5    

28
В помощь разработчику: преобразование табличного документа в запрос

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

Внешняя обработка для создания запроса на основании табличных данных.

22.07.2024    6640    193    ixijixi    10    

38
Поиск дублей по полям ключей шапки, табличных частей

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

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

08.07.2024    2727    ivanov660    9    

22
Итоговый запрос в отчете СКД

Запросы СКД Программист Стажер Система компоновки данных Россия Бесплатно (free)

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

15.05.2024    10219    implecs_team    6    

48
Пропорциональное распределение в запросе с использованием АвтоНомерЗаписи()

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

Часто поступают задачи по произвольному распределению общих сумм. После распределения иногда пропадают копейки. Суть решения добавить АвтоНомерЗаписи() в ВТ распределения, и далее используя функции МАКСИМУМ или МИНИМУМ можем положить разницу копеек в первую или последнюю строку знаменателя распределения.

11.04.2024    3623    andrey_sag    10    

38
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. ZLENKO 398 25.12.14 15:58 Сейчас в теме
Вот в этом отчете по просроченной дебиторке используется расчет по рабочим дням http://infostart.ru/public/117647/

+ – Ответить
2. chmv 25.12.14 16:52 Сейчас в теме
Любопытно. А как на счет соединения в запросе даты+наименование
Например ФизЛицо.Наименование+ФизЛицо.Дата
+ – Ответить
3. ildarovich 7939 26.12.14 00:34 Сейчас в теме
А в чем проблема просто соединить таблицу долгов с "РегистрСведений.РегламентированныйПроизводственныйКалендарь" по условию ДатаКалендаря МЕЖДУ ДатаВозникновенияДолга И &ТекущаяДата ГДЕ Рабочий = ИСТИНА и получить число рабочих дней как КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ДатаКалендаря) ? По идее, при соединении должен использоваться индекс и никаких проблем с производительностью у этого запроса быть не должно.
А вот в предлагаемом решении проблемы есть - время предварительного расчета таблицы "интеграл рабочих дней" квадратично зависит от интервала. Не зря тут ограничение на плюс-минус год стоит. И что будет, если долг имеет двухгодичную давность?
В общем, для убедительности нужно привести проблемный запрос, который вы пытаетесь таким образом оптимизировать и сравнительные замеры времени выполнения запросов. Иначе может оказаться, что предлагается никому не нужное усложнение простого запроса.
user779781; Muhin555; xzorkiix; Badakismati; Valerich; +5 – Ответить
4. ksuman 21 31.12.14 14:01 Сейчас в теме
(3) ildarovich, Вы правильно заметили: ГОД(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря) в условиях - и сервер БД не будет использовать Кластерный индекс по полю даты календаря, что в случае использования системы после десятка лет неоправданно увеличит время выполнения запроса.

Можно использовать Пакетный запрос, только условие надо писать корректно: не так как нам читабельно, а так чтобы быстро выполнялся.
xzorkiix; +1 – Ответить
5. Sergey.Noskov 1411 13.01.15 13:14 Сейчас в теме
Мы таким же запросом определяли, потом таки переделали регистр - добавили несколько ресурсов, соответствующих самым часто востребованным рабочим дням (след рабочий день, третий, 10й)
+ – Ответить
6. Diego_Iv 34 15.01.15 10:19 Сейчас в теме
Мы тоже поначалу использовали аналогичный запрос в отчетах по просроченной задолженности.
На тысяче контрагентов (у контрагента может быть до 10 договоров с разными условиями по срокам оплаты, сроки считаются как по календарным, так и рабочим дням) отчет мог строиться до 30 минут, из-за того что срок погашения долга рассчитывался в момент формирования отчета для каждого документа, сформировавшего этот долг).
В результате сделали так: добавили регистр сведений "сроки погашения". Измерения - Договор контрагента, документ расчетов. Ресурс - Дата погашения.
В момент проведения документа поступления (реализации) подпиской на событие в регистр добавляется запись с уже рассчитанной датой погашения долга по этому документу.

В результате все отчеты по долгам строятся за секунды.
dgolovanov; +1 – Ответить
7. chmv 16.01.15 12:09 Сейчас в теме
Быстрее иначе никак
+ – Ответить
8. vis_tmp 32 26.05.19 16:29 Сейчас в теме
Спасибо, очень хороший метод для расчета числа рабочих дней между двумя датами.
+ – Ответить
9. vis_tmp 32 26.05.19 19:50 Сейчас в теме
А как быть если дата приходится на выходной день, отсутствующий в выборке?
+ – Ответить
10. kozusenok 54 20.04.22 08:20 Сейчас в теме
Недавно тоже столкнулся с проблемой поиска ближайшего рабочего дня к дате. Запрос похож на запрос из публикации, но оставлю здесь на всякий случай. 

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

УПОРЯДОЧИТЬ ПО
	Календарь.ДатаКалендаря

ИНДЕКСИРОВАТЬ ПО
	Календарь.ДатаКалендаря
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ДАТАВРЕМЯ(2022, 4, 1) КАК ДатаДокумента,
	5 КАК ДнейОтсрочки
ПОМЕСТИТЬ ТаблицаОплат

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	ДАТАВРЕМЯ(2022, 4, 9),
	7
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	Оплаты.ДатаДокумента КАК ДатаДокумента,
	Оплаты.ДнейОтсрочки КАК ДнейОтсрочки,
	Календарь.ДатаКалендаря КАК ДатаОплаты
ИЗ
	ТаблицаОплат КАК Оплаты
		ЛЕВОЕ СОЕДИНЕНИЕ БанковскиеДниКалендаря КАК Календарь
		ПО ((Календарь.ДатаКалендаря, Оплаты.ДнейОтсрочки) В
				(ВЫБРАТЬ
					Выборка.ДатаКалендаря КАК МаксДатаКалендаря,
					СУММА(1) КАК НомерРабочегоДня
				ИЗ
					БанковскиеДниКалендаря КАК Выборка ЛЕВОЕ СОЕДИНЕНИЕ БанковскиеДниКалендаря КАК ПодВыборка
						ПО
							ПодВыборка.ДатаКалендаря >= Оплаты.ДатаДокумента
								И ПодВыборка.ДатаКалендаря <= Выборка.ДатаКалендаря
				ГДЕ
					Выборка.ДатаКалендаря >= Оплаты.ДатаДокумента
				СГРУППИРОВАТЬ ПО
							Выборка.ДатаКалендаря))
Показать
+ – Ответить
11. kozusenok 54 20.04.22 20:47 Сейчас в теме
Если не нужно считать день даты документа то ставим "> Оплаты.ДатаДокумента" 

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

УПОРЯДОЧИТЬ ПО
    Календарь.ДатаКалендаря

ИНДЕКСИРОВАТЬ ПО
    Календарь.ДатаКалендаря
;

////////////////////////////////////////////////////////////­­////////////////////
ВЫБРАТЬ
    ДАТАВРЕМЯ(2022, 4, 1) КАК ДатаДокумента,
    5 КАК ДнейОтсрочки
ПОМЕСТИТЬ ТаблицаОплат

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ДАТАВРЕМЯ(2022, 4, 9),
    7
;

////////////////////////////////////////////////////////////­­////////////////////
ВЫБРАТЬ
    Оплаты.ДатаДокумента КАК ДатаДокумента,
    Оплаты.ДнейОтсрочки КАК ДнейОтсрочки,
    Календарь.ДатаКалендаря КАК ДатаОплаты
ИЗ
    ТаблицаОплат КАК Оплаты
        ЛЕВОЕ СОЕДИНЕНИЕ БанковскиеДниКалендаря КАК Календарь
        ПО ((Календарь.ДатаКалендаря, Оплаты.ДнейОтсрочки) В
                (ВЫБРАТЬ
                    Выборка.ДатаКалендаря КАК МаксДатаКалендаря,
                    СУММА(1) КАК НомерРабочегоДня
                ИЗ
                    БанковскиеДниКалендаря КАК Выборка ЛЕВОЕ СОЕДИНЕНИЕ БанковскиеДниКалендаря КАК ПодВыборка
                        ПО
                            ПодВыборка.ДатаКалендаря > Оплаты.ДатаДокумента
                                И ПодВыборка.ДатаКалендаря <= Выборка.ДатаКалендаря
                ГДЕ
                    Выборка.ДатаКалендаря > Оплаты.ДатаДокумента
                СГРУППИРОВАТЬ ПО
                            Выборка.ДатаКалендаря))
Показать
+ – Ответить
Оставьте свое сообщение