Дополнение остатков периодами в запросе

14.05.15

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

Ниже приведен один из возможных вариантов решения задачи получения остатков по периодам (без "разрывов") в запросе с объединением с подзапросом к другой таблице.

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование По подписке [?] Купить один файл
Демонстрационная база
.dt 44,80Kb ver:1.0.0.0
2
2 Скачать (1 SM) Купить за 1 850 руб.

В целях демонстрации решения задачи разработана простая конфигурация, состоящая из двух документов, одного справочника, одного регистра накопления вида "Остатки" и отчета.

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

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

Кратко о конфигурации:

В справочник заносятся организации, документом "Поступление (списание) денежных средств" отражается факт поступления или списания денежных средств на [условный] счет организации, документом "Черная прибыль" дополнительно вносятся к учету некоторые средства.


Итак, имеется таблица регистра накопления со следующим содержимым:

 Содержимое регистра накопления

 

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

Документы "Черная прибыль"

 

Необходимо сформировать отчет, который будет включать следующие данные:

 

Организация   Начальный остаток средств    Приход средств   Расход средств   Конечный остаток средств   Черная прибыль 
Месяц

 

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

 

Создаем отчет с использованием системы компоновки данных и конструируем во внутренностях такой запрос:

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

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

ВЫБРАТЬ
	ЧёрнаяПрибыль.Организация,
	НАЧАЛОПЕРИОДА(ЧёрнаяПрибыль.Дата, МЕСЯЦ),
	NULL,
	NULL,
	NULL,
	NULL,
	ЧёрнаяПрибыль.Сумма
ИЗ
	Документ.ЧёрнаяПрибыль КАК ЧёрнаяПрибыль
ГДЕ
	ЧёрнаяПрибыль.ПометкаУдаления = ЛОЖЬ
{ГДЕ
	ЧёрнаяПрибыль.Организация.* КАК Организация,
	(ЧёрнаяПрибыль.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)),
	(ЧёрнаяПрибыль.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ))}

В конструкторе СКД выбираем необходимые поля для вывода в отчет, создаем группировки по полям Организация и Месяц, для группировки по месяцам устанавливаем дополнение периода Месяц.

Вроде бы, всё просто. Некоторые спрашивают, можно ли в таких случаях в запросе делать левое соединение. Ответ: не запрещается, но если сделать соединение к таблице, в которой нет данных (а такое может быть), получим пустой отчет.


Смотрим, что получилось в нашем случае:

Первый вариант отчета

(в условном оформлении задан формат поля "ПериодМесяц": ДФ='MMMM yyyy')

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

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

Получаем такой запрос (пояснения ниже):

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ЧёрнаяПрибыль.Организация КАК Организация,
	НАЧАЛОПЕРИОДА(ЧёрнаяПрибыль.Дата, МЕСЯЦ) КАК ПериодМесяц,
	ЧёрнаяПрибыль.Сумма КАК ЧернаяПрибыль
ПОМЕСТИТЬ ДанныеИзДокументов
ИЗ
	Документ.ЧёрнаяПрибыль КАК ЧёрнаяПрибыль
ГДЕ
	ЧёрнаяПрибыль.ПометкаУдаления = ЛОЖЬ
{ГДЕ
	ЧёрнаяПрибыль.Организация.* КАК Организация,
	(ЧёрнаяПрибыль.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)),
	(ЧёрнаяПрибыль.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ))}
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	0 КАК Цифра
ПОМЕСТИТЬ Цифры

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	1

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	2

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	3

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	4

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	5

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	6

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	7

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	8

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	9
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Тысячи.Цифра * 1000 + Сотни.Цифра * 100 + Десятки.Цифра * 10 + Единицы.Цифра КАК Дней
ПОМЕСТИТЬ СписокДней
ИЗ
	Цифры КАК Тысячи,
	Цифры КАК Сотни,
	Цифры КАК Десятки,
	Цифры КАК Единицы
ГДЕ
	Тысячи.Цифра * 1000 + Сотни.Цифра * 100 + Десятки.Цифра * 10 + Единицы.Цифра <= РАЗНОСТЬДАТ(ДАТАВРЕМЯ(2000, 1, 1), ДОБАВИТЬКДАТЕ(&КонецПериода, ГОД, 10), МЕСЯЦ)
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(2000, 1, 1), МЕСЯЦ, СписокДней.Дней) КАК Период
ПОМЕСТИТЬ ВсеМесяцы
ИЗ
	СписокДней КАК СписокДней
;

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

СГРУППИРОВАТЬ ПО
	ДанныеИзРегистра.Организация,
	ДанныеИзРегистра.СуммаКонечныйОстаток,
	ДанныеИзРегистра.ПериодМесяц
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВсеИнтервалы.Организация,
	ВсеИнтервалы.СуммаКонечныйОстаток,
	ВсеИнтервалы.ПериодМесяц,
	ВсеИнтервалы.ПериодМесяц1
ПОМЕСТИТЬ ИнтервалыБольшеМесяца
ИЗ
	ВсеИнтервалы КАК ВсеИнтервалы
ГДЕ
	РАЗНОСТЬДАТ(ВсеИнтервалы.ПериодМесяц, ВсеИнтервалы.ПериодМесяц1, МЕСЯЦ) > 1
;

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

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

СГРУППИРОВАТЬ ПО
	ДанныеИзДокументов.ПериодМесяц,
	ДанныеИзДокументов.Организация,
	ДанныеИзРегистра.СуммаКонечныйОстаток
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	НедостающиеПериодыИСальдоПоДокументам.ПериодМесяц,
	НедостающиеПериодыИСальдоПоДокументам.Организация,
	МАКСИМУМ(НедостающиеПериодыИСальдоПоДокументам.ПериодМесяц1) КАК ПериодМесяц1
ПОМЕСТИТЬ НедостающиеПериодыБезКонОст
ИЗ
	НедостающиеПериодыИСальдоПоДокументам КАК НедостающиеПериодыИСальдоПоДокументам

СГРУППИРОВАТЬ ПО
	НедостающиеПериодыИСальдоПоДокументам.ПериодМесяц,
	НедостающиеПериодыИСальдоПоДокументам.Организация
;

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

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

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

ВЫБРАТЬ
	НедостающиеПериоды.Организация,
	НедостающиеПериоды.Период,
	НедостающиеПериоды.СуммаКонечныйОстаток,
	0,
	0,
	НедостающиеПериоды.СуммаКонечныйОстаток
ИЗ
	НедостающиеПериоды КАК НедостающиеПериоды

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

ВЫБРАТЬ
	НеДостающееСальдоПоДокументам.Организация,
	НеДостающееСальдоПоДокументам.ПериодМесяц,
	НеДостающееСальдоПоДокументам.СуммаКонечныйОстаток,
	0,
	0,
	НеДостающееСальдоПоДокументам.СуммаКонечныйОстаток
ИЗ
	НедостающееСальдоПоДокументам КАК НеДостающееСальдоПоДокументам
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	СальдоВсеПериоды.Организация КАК Организация,
	СальдоВсеПериоды.ПериодМесяц КАК ПериодМесяц,
	СальдоВсеПериоды.СуммаНачальныйОстаток КАК СуммаНачальныйОстаток,
	СальдоВсеПериоды.СуммаПриход КАК СуммаПриход,
	СальдоВсеПериоды.СуммаРасход КАК СуммаРасход,
	СальдоВсеПериоды.СуммаКонечныйОстаток КАК СуммаКонечныйОстаток,
	NULL КАК ЧернаяПрибыль
ПОМЕСТИТЬ Данные
ИЗ
	СальдоВсеПериоды КАК СальдоВсеПериоды

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

ВЫБРАТЬ
	ДанныеИзДокументов.Организация,
	ДанныеИзДокументов.ПериодМесяц,
	NULL,
	NULL,
	NULL,
	NULL,
	ДанныеИзДокументов.ЧернаяПрибыль
ИЗ
	ДанныеИзДокументов КАК ДанныеИзДокументов
;

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

СГРУППИРОВАТЬ ПО
	Данные.Организация,
	Данные.ПериодМесяц

 

 Пояснения к запросу:

В разные временные таблицы выбираются данные из регистра и документов. Затем происходят манипуляции с этими данными - вычисление недостающих периодов и добавление данных по ним в результирующую выборку.

Пояснения к временным таблицам:

  • ДанныеИзРегистра - данные из регистра накопления (остатки и обороты)
  • ДанныеИзДокументов - данные из документов (черная прибыль)
  • ВсеМесяцы - таблица в которой содержатся все месяцы из интервала Январь 2000 - (Конечная дата отчета + 10 лет); этот интервал можно менять под конкретные нужды
  • ВсеИнтервалы - интервалы [в количестве месяцев] между периодами в результате запроса к регистру накопления
  • ИнтервалыБольшеМесяца - интервалы [в количестве месяцев] между периодами в результате запроса к регистру накопления, где количество месяцев в интервале больше 1
  • НедостающиеПериоды - недостающие периоды в данных из регистра накопления и конечное сальдо по этим периодам (оно будет равно начальному в отчете)
  • НедостающиеПериодыИСальдоПоДокументам - периоды (и сальдо по ним), которых нет в регистре, но есть в документах
  • НедостающиеПериодыБезКонОст - свернутая таблица с недостающими периодами по документам без остатков
  • НедостающееСальдоПоДокументам - свернутая таблица с недостающими периодами по документам и остатками
  • СальдоВсеПериоды - таблица в которой содержатся все недостающие периоды (и по документам и "разрывы" в регистре) с остатками
  • Данные - объединение данных из регистра (с дополненными периодами) с данными из документов

 

В данной реализации необходимо, чтобы была заполнена конечная дата формирования отчета.

Результат формирования отчета:

 Конечный вариант отчета

 

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

В публикацию включена демонстрационная база.

 

дополнение периодов в запросе с объединением

См. также

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

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

12000 руб.

02.09.2020    169274    937    403    

905

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

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

18.10.2024    11394    sergey279    18    

65

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

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

11.10.2024    6338    XilDen    36    

83

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

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

16.08.2024    9068    user1840182    5    

28

Математика и алгоритмы Запросы Программист Платформа 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. jobkostya1c_ERP 100 15.05.15 09:18 Сейчас в теме
Все конечно хорошо, но лучше бы переименовали (хоть синоним документа не "Черная прибыль", а хоть "Накладные расходы (и что-то про цвет)" иначе первым делом в глаза бросится для случайных людей :)
2. AnryMc 849 15.05.15 09:41 Сейчас в теме
(1) kostyaomsk, Это, наверно, "очепятка" - вместо "Черная прибыль", следует читать "Черновая прибыль"...
3. xaozai 15.05.15 09:51 Сейчас в теме
(1), (2), Это может быть прибыль, получаемая от продаж "черного золота" на какой-нибудь там бирже...
le_; WKBAPKA; +2 Ответить
4. WKBAPKA 215 16.05.15 10:39 Сейчас в теме
Документ "Черная прибыль"... гы :)
5. mrBart 33 18.05.15 08:22 Сейчас в теме
В отчете "Регламентированный отчет имущество" в разделе БГУ есть запрос который собирает отстатки по периодам (на 13 дат), он симпатичнее и проще.
6. qwinter 684 18.05.15 19:50 Сейчас в теме
Чукча не читатель, чукча писатель ©

http://infostart.ru/public/306536/
Оставьте свое сообщение