v8: Полные остатки по периоду во вложенном запросе
Ключевые слова: запрос,остатки,полные,день,период
Как получить остатки по периоду когда не было движения?
Многие знают что остатки по периоду с дополнением можно получить таким запросом
ВЫБРАТЬ
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.ДоговорКонтрагента.Владелец КАК Контрагент,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.СуммаВзаиморасчетовКонечныйОстаток КАК Сумма,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Период
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&ДатаНач, &ДатаКон, День, , ) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты
ИТОГИ
СУММА(Сумма)
ПО
ОБЩИЕ,
Период ПЕРИОДАМИ(ДЕНЬ, &ДатаНач, &ДатаКон),
Контрагент,
ДоговорКонтрагента
Но что делать когда необходимо их получить во вложенном запросе, или посчитать количество дней с отрицательными остатками?
Предлагаю следующий вариант. Регистр сведений КурсыВалют должен быть заполнен всеми днями.
ВЫБРАТЬ
Дебиторка.СуммаВзаиморасчетовКонечныйОстаток КАК Долг,
КурсыВалют.Период КАК Период,
Дебиторка.ДоговорКонтрагента КАК ДоговорКонтрагента,
Дебиторка.ДоговорКонтрагента.Владелец КАК Контрагент,
ВЫБОР
КОГДА Дебиторка.СуммаВзаиморасчетовКонечныйОстаток < 0
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК КвоОтрицательных
ИЗ
РегистрСведений.КурсыВалют КАК КурсыВалют
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
Оборот1.Дата КАК ДатаС,
МИНИМУМ(ВЫБОР
КОГДА Оборот2.Дата ЕСТЬ NULL
ТОГДА КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
ИНАЧЕ КОНЕЦПЕРИОДА(ДОБАВИТЬКДАТЕ(Оборот2.Дата, ДЕНЬ, -1), ДЕНЬ)
КОНЕЦ) КАК ДатаПо,
Оборот1.СуммаВзаиморасчетовКонечныйОстаток КАК СуммаВзаиморасчетовКонечныйОстаток,
Оборот1.ДоговорКонтрагента КАК ДоговорКонтрагента
ИЗ
(ВЫБРАТЬ
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Дата,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.СуммаВзаиморасчетовКонечныйОстаток КАК СуммаВзаиморасчетовКонечныйОстаток,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , {(ДоговорКонтрагента.Организация).* КАК Организация, (ДоговорКонтрагента.Владелец).* КАК Контрагент, (ДоговорКонтрагента).*}) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты) КАК Оборот1
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Дата,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , {(ДоговорКонтрагента.Организация).* КАК Организация, (ДоговорКонтрагента.Владелец).* КАК Контрагент, (ДоговорКонтрагента).*}) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты) КАК Оборот2
ПО Оборот1.Дата < Оборот2.Дата
И Оборот1.ДоговорКонтрагента = Оборот2.ДоговорКонтрагента
СГРУППИРОВАТЬ ПО
Оборот1.Дата,
Оборот1.СуммаВзаиморасчетовКонечныйОстаток,
Оборот1.ДоговорКонтрагента) КАК Дебиторка
ПО (КурсыВалют.Период МЕЖДУ Дебиторка.ДатаС И Дебиторка.ДатаПо)
ГДЕ
КурсыВалют.Период МЕЖДУ &НачалоПериода И &КонецПериода
И КурсыВалют.Валюта = &Валюта
ИТОГИ
СУММА(Долг),
СУММА(КвоОтрицательных)
ПО
ОБЩИЕ,
Контрагент,
ДоговорКонтрагента,
Период
АВТОУПОРЯДОЧИВАНИЕ
Заметьте, что в итогах для слова Период нет дополнения.
А что делать когда нет возможности использовать дополнительный регистр?
Можно построить таблицу с датами и в SQL.
Вот так будет выглядеть запрос.
ВЫБРАТЬ
Дебиторка.СуммаВзаиморасчетовКонечныйОстаток КАК Долг,
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, РазностьДат.НомерДняИзДиапазона) КАК Период,
Дебиторка.ДоговорКонтрагента КАК ДоговорКонтрагента,
ПРЕДСТАВЛЕНИЕ(Дебиторка.ДоговорКонтрагента),
Дебиторка.ДоговорКонтрагента.Владелец КАК Контрагент,
ПРЕДСТАВЛЕНИЕ(Дебиторка.ДоговорКонтрагента.Владелец) КАК КонтрагентПредставление,
ВЫБОР
КОГДА Дебиторка.СуммаВзаиморасчетовКонечныйОстаток < 0
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК КвоОтрицательных
ИЗ
(ВЫБРАТЬ
Оборот1.Дата КАК ДатаС,
МИНИМУМ(ВЫБОР
КОГДА Оборот2.Дата ЕСТЬ NULL
ТОГДА КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
ИНАЧЕ КОНЕЦПЕРИОДА(ДОБАВИТЬКДАТЕ(Оборот2.Дата, ДЕНЬ, -1), ДЕНЬ)
КОНЕЦ) КАК ДатаПо,
Оборот1.СуммаВзаиморасчетовКонечныйОстаток КАК СуммаВзаиморасчетовКонечныйОстаток,
Оборот1.ДоговорКонтрагента КАК ДоговорКонтрагента
ИЗ
(ВЫБРАТЬ
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Дата,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.СуммаВзаиморасчетовКонечныйОстаток КАК СуммаВзаиморасчетовКонечныйОстаток,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , {(ДоговорКонтрагента.Организация).* КАК Организация, (ДоговорКонтрагента.Владелец).* КАК Контрагент, (ДоговорКонтрагента).*}) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты) КАК Оборот1
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Дата,
ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , {(ДоговорКонтрагента.Организация).* КАК Организация, (ДоговорКонтрагента.Владелец).* КАК Контрагент, (ДоговорКонтрагента).*}) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты) КАК Оборот2
ПО Оборот1.Дата < Оборот2.Дата
И Оборот1.ДоговорКонтрагента = Оборот2.ДоговорКонтрагента
СГРУППИРОВАТЬ ПО
Оборот1.Дата,
Оборот1.СуммаВзаиморасчетовКонечныйОстаток,
Оборот1.ДоговорКонтрагента) КАК Дебиторка
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d КАК НомерДняИзДиапазона
ИЗ
(ВЫБРАТЬ
0 КАК a
ОБЪЕДИНИТЬ
ВЫБРАТЬ
1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
2
ОБЪЕДИНИТЬ
ВЫБРАТЬ
3
ОБЪЕДИНИТЬ
ВЫБРАТЬ
4
ОБЪЕДИНИТЬ
ВЫБРАТЬ
5
ОБЪЕДИНИТЬ
ВЫБРАТЬ
6
ОБЪЕДИНИТЬ
ВЫБРАТЬ
7
ОБЪЕДИНИТЬ
ВЫБРАТЬ
8
ОБЪЕДИНИТЬ
ВЫБРАТЬ
9) КАК aa
ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
0 КАК b
ОБЪЕДИНИТЬ
ВЫБРАТЬ
1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
2
ОБЪЕДИНИТЬ
ВЫБРАТЬ
3
ОБЪЕДИНИТЬ
ВЫБРАТЬ
4
ОБЪЕДИНИТЬ
ВЫБРАТЬ
5
ОБЪЕДИНИТЬ
ВЫБРАТЬ
6
ОБЪЕДИНИТЬ
ВЫБРАТЬ
7
ОБЪЕДИНИТЬ
ВЫБРАТЬ
8
ОБЪЕДИНИТЬ
ВЫБРАТЬ
9) КАК bb
ПО (ИСТИНА)
ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
0 КАК c
ОБЪЕДИНИТЬ
ВЫБРАТЬ
1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
2
ОБЪЕДИНИТЬ
ВЫБРАТЬ
3
ОБЪЕДИНИТЬ
ВЫБРАТЬ
4
ОБЪЕДИНИТЬ
ВЫБРАТЬ
5
ОБЪЕДИНИТЬ
ВЫБРАТЬ
6
ОБЪЕДИНИТЬ
ВЫБРАТЬ
7
ОБЪЕДИНИТЬ
ВЫБРАТЬ
8
ОБЪЕДИНИТЬ
ВЫБРАТЬ
9) КАК cc
ПО (ИСТИНА)
ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
0 КАК d
ОБЪЕДИНИТЬ
ВЫБРАТЬ
1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
2
ОБЪЕДИНИТЬ
ВЫБРАТЬ
3
ОБЪЕДИНИТЬ
ВЫБРАТЬ
4
ОБЪЕДИНИТЬ
ВЫБРАТЬ
5
ОБЪЕДИНИТЬ
ВЫБРАТЬ
6
ОБЪЕДИНИТЬ
ВЫБРАТЬ
7
ОБЪЕДИНИТЬ
ВЫБРАТЬ
8
ОБЪЕДИНИТЬ
ВЫБРАТЬ
9) КАК dd
ПО (ИСТИНА)
ГДЕ
aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d <= &КолДнейМеждуДатами) КАК РазностьДат
ПО (ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, РазностьДат.НомерДняИзДиапазона) МЕЖДУ Дебиторка.ДатаС И Дебиторка.ДатаПо)
ИТОГИ
СУММА(Долг),
СУММА(КвоОтрицательных)
ПО
ОБЩИЕ,
Контрагент,
ДоговорКонтрагента,
Период
АВТОУПОРЯДОЧИВАНИЕ |