Запрос с остатками по дням
Если что — не ругайте. Пытаюсь объяснить как могу.
Запрос выводит разные значения остатков по дням (остаток товара, остатот денежных средств, остаток по подотчетникам, остатки по взаиморасчетам).
Состоит из подзапросов — один из которых остатки по взаиморасчетам.
Т.е. из него необходимо получить результат в виде:
01.02.11 5 000,00
02.02.11 10 000,00
03.02.11 -500,00
Где суммы это общая сумма по взаиморасчетам с конрагентами на конкретную дату.
Сделал подзапрос всё вроде как нормально, но необходимо при получении взаиморасчетов исключить одну группу контрагентов — при этом всё стало выдавать совершенно не реальные суммы. Стал писать отдельно запрос по взаиморасчетам, чтобы прийти к правильному тексту запроса.
Получился запрос
ВЫБРАТЬ
ИсточникДанных.Период КАК Период,
ИсточникДанных.СуммаВзаиморасчетовКонечныйОстаток КАК Сумма,
ИсточникДанных.СуммаУпрКонечныйОстаток КАК СуммаУпр
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&ДатаНач, &ДатаКон, День, , ) КАК ИсточникДанных
ГДЕ
НЕ(ИсточникДанных.Контрагент.Родитель = &Контрагент)
ИТОГИ
СУММА(Сумма),
СУММА(СуммаУпр)
ПО
Период ПЕРИОДАМИ(ДЕНЬ, &ДатаНач, &ДатаКон)
Но в консоле запросов результрующая таблица имеет в первой строке даты необходимую сумму взаиморасчтов, а потом с этой же датой идут строки как я понимаю с суммами движений документов за эту дату — они мне не ныжны.
Источник
Как запросом получить остаток товара на каждый день выбранного периода?
Как запросом получить остаток товара на каждый день выбранного периода? Ну примерно в таком виде
01.01.2011 120
02.01.2011 120
03.01.2011 0
04.01.2011 0
05.01.2011 50
.
31.01.2011 32
А вообще нужно еще остаток на первую и последнюю дату в выборке поделить пополам, это возможно?
Если нет, то хотя бы просто остатки в таком вот виде выдернуть.
Используй ОстаткиИОбороты, в параметрах виртуальной таблицы поставь «День», как остатки бери «КонечныйОстаток».
Что-то вроде этого:
А можно пример запроса?
(5)Там на примере СКД, а мне в запросе это получить надо по каждой номенклатуре для дальнейшей обработки
Если в запросе, то тоже есть вариант, примерно так:
Запрос = Новый Запрос;
Запрос.Текст =
«ВЫБРАТЬ
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура,
| ТоварыНаСкладахОстаткиИОбороты.Период КАК Период,
| ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(, , День, , ) КАК ТоварыНаСкладахОстаткиИОбороты
|ИТОГИ ПО
| Период ПЕРИОДАМИ(ДЕНЬ, , )»;
А средние запасы в указанной обработке расчитаны иначе, чем мне нужно, насколько я понимаю. Вот выдержка из моего ТЗ:
«Правильная формула для подсчета среднего товарного запаса такая:
ТЗ1, ТЗ2, … ТЗn — величина товарного запаса на отдельные даты анализируемого периода,
n – количество дат в периоде.»
Из за этого и весь сыр-бор.
(12)
Если я правильно понял смысл формулы,то он в том, что при усреднении остатков по периодам внутри интервала мы усредняем не остатки на конец каждого периода,а среднее остатков на начало и конец каждого периода. То есть мы не можем решить, что есть остаток для, например,заданного дня: остаток на начало дня или остаток на конец дня. Поэтому остатком считаем среднее остатка на начало и конец. Отсюда и следует предложенная формула. Для выкладок и написания запроса ее удобнее записать так:
S`= (So/2 + S1 + S2 + S3 + . + Sn/2) / n,
начав отсчет с нуля. Si — это остаток на момент i. So — начальный остаток на начало интервала, S1 — остаток на конец первого периода. Если D1 — это оборот на первом периоде,то S1 = So + D1 и так далее. Тогда знаменатель среднего можно написать так:
So/2
+ So + D1
+ So + D1 + D2
+ So + D1 + D2 + D3
.
+ So/2 + D1/2 + D2/2 + D3/2 + . + Dn/2 =
= So*n + D1*(n-1/2) + D2*(n-1/2-1) + D3*(n-1/2-2) + . + Dn*(1/2) =
= So*n + Сумма по i=1,n Di*(n+1/2-i).
Разделив на n, получим
S`= (So/2 + S1 + S2 + S3 + . + Sn/2) / n = So + Сумма по i=1,n Di*(n+1/2-i)/n.
В итоге запрос должен выглядеть так:
Конечно, желательно все еще раз проверить и протестировать на проверяемых вручную данных.
Источник
Как получить остатки на каждый день по регистру накопления?
Как получить остатки на каждый день по регистру накопления?
Например:
«ВЫБРАТЬ
| ТоварыНаСкладахОстаткиИОбороты.Номенклатура,
| ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток,
| ТоварыНаСкладахОстаткиИОбороты.Период
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&ДатаНач, &ДатаКон, День, , ) КАК ТоварыНаСкладахОстаткиИОбороты»;
Как Вы обычно решаете эту проблему?
(1) barm, Подобный запрос, для получения остатков на каждый день, для бухгалтерии, смысл, я думаю, уловите:
ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Период,
ХозрасчетныйОстаткиИОбороты.ВалютнаяСуммаНачальныйОстатокДт,
ХозрасчетныйОстаткиИОбороты.ВалютнаяСуммаНачальныйОстатокКт,
ХозрасчетныйОстаткиИОбороты.ВалютнаяСуммаКонечныйОстатокДт,
ХозрасчетныйОстаткиИОбороты.ВалютнаяСуммаКонечныйОстатокКт,
ХозрасчетныйОстаткиИОбороты.Валюта,
ХозрасчетныйОстаткиИОбороты.Субконто1,
ХозрасчетныйОстаткиИОбороты.Субконто2,
ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстатокДт,
ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстатокКт,
ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстатокДт,
ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстатокКт
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(
&ДатаНачала,
&ДатаОкончания,
День,
ДвиженияИГраницыПериода,
Счет В ИЕРАРХИИ (&Счет),
,
Субконто1 = &Контрагент
И Субконто2 = &Договор
И Организация = &Организация) КАК ХозрасчетныйОстаткиИОбороты
;
X
ВЫБРАТЬ
РегламентированныйПроизводственныйКалендарь.ДатаКалендаря,
ВложенныйЗапрос.Субконто1,
ВложенныйЗапрос.Субконто2,
ВложенныйЗапрос.Валюта
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь,
(ВЫБРАТЬ
ВТ_Остатки.Субконто1 КАК Субконто1,
ВТ_Остатки.Субконто2 КАК Субконто2,
ВТ_Остатки.Валюта КАК Валюта
ИЗ
ВТ_Остатки КАК ВТ_Остатки
СГРУППИРОВАТЬ ПО
ВТ_Остатки.Субконто1,
ВТ_Остатки.Субконто2,
ВТ_Остатки.Валюта) КАК ВложенныйЗапрос
ГДЕ
РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ &ДатаНачала И &ДатаОкончания
;
X
ВЫБРАТЬ
ВложенныйЗапрос.Субконто1 КАК Контрагент,
ВложенныйЗапрос.Субконто2 КАК Договор,
ВложенныйЗапрос.Дата КАК Дата,
ЕСТЬNULL(ВТ_Остатки.ВалютнаяСуммаНачальныйОстатокДт, 0) КАК ВалютныйОстатокНачалоДт,
ЕСТЬNULL(ВТ_Остатки.ВалютнаяСуммаНачальныйОстатокКт, 0) КАК ВалютныйОстатокНачалоКт,
ЕСТЬNULL(ВТ_Остатки1.ВалютнаяСуммаКонечныйОстатокДт, 0) КАК ВалютныйОстатокКонецДт,
ЕСТЬNULL(ВТ_Остатки1.ВалютнаяСуммаКонечныйОстатокКт, 0) КАК ВалютныйОстатокКонецКт,
ЕСТЬNULL(ВТ_Остатки.СуммаНачальныйОстатокДт, 0) КАК РублевыйОстатокНачалоДт,
ЕСТЬNULL(ВТ_Остатки.СуммаНачальныйОстатокКт, 0) КАК РублевыйОстатокНачалоКт,
ЕСТЬNULL(ВТ_Остатки1.СуммаКонечныйОстатокДт, 0) КАК РублевыйОстатокКонецДт,
ЕСТЬNULL(ВТ_Остатки1.СуммаКонечныйОстатокКт, 0) КАК РублевыйОстатокКонецКт
ИЗ
(ВЫБРАТЬ
МИНИМУМ(ВТ_Остатки.Период) КАК ПериодНач,
МАКСИМУМ(ВТ_Остатки1.Период) КАК ПериодКон,
ВТ_Календарь.ДатаКалендаря КАК Дата,
ВТ_Календарь.Субконто1 КАК Субконто1,
ПРЕДСТАВЛЕНИЕ(ВТ_Календарь.Субконто1) КАК Субконто1Представление,
ВТ_Календарь.Субконто2 КАК Субконто2,
ПРЕДСТАВЛЕНИЕ(ВТ_Календарь.Субконто2) КАК Субконто2Представление,
ВТ_Календарь.Валюта КАК Валюта,
ПРЕДСТАВЛЕНИЕ(ВТ_Календарь.Валюта) КАК ВалютаПредставление
ИЗ
ВТ_Календарь КАК ВТ_Календарь
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
ПО ВТ_Календарь.ДатаКалендаря = ВТ_Остатки1.Период
И ВТ_Календарь.Субконто2 >= ВТ_Остатки1.Субконто2
СГРУППИРОВАТЬ ПО
ВТ_Календарь.ДатаКалендаря,
ВТ_Календарь.Субконто1,
ВТ_Календарь.Субконто2,
ВТ_Календарь.Валюта) КАК ВложенныйЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
ПО ВложенныйЗапрос.ПериодНач = ВТ_Остатки.Период
И ВложенныйЗапрос.Субконто2 = ВТ_Остатки.Субконто2
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки1
ПО ВложенныйЗапрос.ПериодКон = ВТ_Остатки1.Период
И ВложенныйЗапрос.Субконто2 = ВТ_Остатки1.Субконто2
УПОРЯДОЧИТЬ ПО
Контрагент,
Договор,
Дата
Источник
Запрос для получения остатков по дням даже если не было движений
Сразу скажу, что ни в коей мере не претендую на авторство и оригинальность — почти уверен что все это уже давно есть на просторах интернета, но когда решение такой задачи понадобилось мне самому — сходу ответа не нашел, скомпилировал из кусков решение, и предоставляю его тут — может кому и пригодится. Так же не претендую на быстродействие — меня оное устроило в моей задаче, однако думаю можно поработать над оптимизацией. Запрос писался на самопальной базе, поэтому имена регистров и его измерений отличается от типовых, но думаю переименовать по тексту запроса труда не составит. Готовый запрос с выводом — в обработке, засунутой в качестве вложения к этой статейке.
Итак — ситуация которая может встретиться любому разработчику. Требуется отчет показывающий остатки товаров (денег, взаиморасчетов — чего угодно) на каждый день (или какой еще отрезок времени) в течении заданного периода отчета. Задача в общем-то несложная, но из области тех, которые не решаются просто получением данных из какой-либо таблицы регистра.
Виртуальная таблица регистра накопления, которая может хоть как-то дать данные по остаткам в периоде и попериодно — это таблица «Остатки и обороты» (прошу простить профи за ликбез — но вдруг попадется начинающий читатель). Однако и она не подходит для нашей задачи в чистом виде. Дело в том, что виртуальная таблица остатков и оборотов за заданный период выдаст только записи с данными по остаткам на начало заданного периода, на конец заданного периода, и на те даты, в которые были движения в этом периоде. То есть для примера со скрина, результат получения данных (остатка на конец) из виртуальной таблицы «Остатки и обороты» за май 2016-го с периодичностью в день, будет следующим:
| Период | Номенклатура | Количество |
| 01.05.2016 | Товар 01 | 4 |
| 11.05.2016 | Товар 01 | 3 |
| 16.05.2016 | Товар 01 | 2 |
| 31.05.2016 | Товар 01 | 2 |
Нам же в рамках нашей задачи нужна таблица, показывающая остаток на каждый день месяца:
| Период | Номенклатура | Количество |
| 01.05.2016 | Товар 01 | 4 |
| 02.05.2016 | Товар 01 | 4 |
| 03.05.2016 | Товар 01 | 4 |
| 04.05.2016 | Товар 01 | 4 |
| 05.05.2016 | Товар 01 | 4 |
| 06.05.2016 | Товар 01 | 4 |
| 07.05.2016 | Товар 01 | 4 |
| 08.05.2016 | Товар 01 | 4 |
| 09.05.2016 | Товар 01 | 4 |
| 10.05.2016 | Товар 01 | 4 |
| 11.05.2016 | Товар 01 | 3 |
| 12.05.2016 | Товар 01 | 3 |
| 13.05.2016 | Товар 01 | 3 |
| 14.05.2016 | Товар 01 | 3 |
| 15.05.2016 | Товар 01 | 3 |
| 16.05.2016 | Товар 01 | 2 |
| 17.05.2016 | Товар 01 | 2 |
| 18.05.2016 | Товар 01 | 2 |
| 19.05.2016 | Товар 01 | 2 |
| 20.05.2016 | Товар 01 | 2 |
| 21.05.2016 | Товар 01 | 2 |
| 22.05.2016 | Товар 01 | 2 |
| 23.05.2016 | Товар 01 | 2 |
| 24.05.2016 | Товар 01 | 2 |
| 25.05.2016 | Товар 01 | 2 |
| 26.05.2016 | Товар 01 | 2 |
| 27.05.2016 | Товар 01 | 2 |
| 28.05.2016 | Товар 01 | 2 |
| 29.05.2016 | Товар 01 | 2 |
| 30.05.2016 | Товар 01 | 2 |
| 31.05.2016 | Товар 01 | 2 |
Посмотрим как мы можем это получить. Вот текст самого запроса:
Запрос идет по временным таблицам — как бы даже не исбыточно, но так читается удобнее. По таблицам:
1. Получаем таблицу с дневными периодами — только даты начала каждого дня (это точно не мое!);
2. Получаем таблицу номенклатуры из справочника (чтобы выводить в результат даже ту номенклатуру, по которой записей в регистре отродясь не было);
3. Соединяем эти две таблицы полным соединением, без указания связи (для педантов: Можно добавить строчку «ПО ИСТИНА» — рещультат будет тот же);
4. Соединяем левым соединением таблицу периодов с номенклатурой с виртуальной таблицей регистра «Остатки и обороты». Периодичность таблицы — день, метод дополнения не указываем. Период выборки задаем тот же, что и для таблицы периодов. Тут основная хитрость в связи. Связываем таблицы по Номенклатуре и Периоду, но период сравниваем не на равенство, а на ПериодТаблицыПериодов >= ПериодТаблицыОстатковИОборотовРегистра
Таким образом например для даты 15.05.2016 мы получим набор строк — все попадающие по условию связи:
| Номенклатура | Период (таблица периодов) | Период (таблица регистра) |
| Товар 01 | 15.05.2016 | 01.05.2016 |
| Товар 01 | 15.05.2016 | 11.05.2016 |
Сами цифирки в этой таблице мы не вытаскиваем — они нам пока не нужны. А эту табличку мы группируем по периоду из таблицы периодов и номенклатуре. Период таблицы регистра получаем максимальный «МАКСИМУМ(ОстаткиТоваровОстаткиИОбороты.Период) КАК ПериодРегистра». Таким образом наша табличка примера схлопывается до одной строки:
| Номенклатура | Период (таблица периодов) | Период (таблица регистра) |
| Товар 01 | 15.05.2016 | 11.05.2016 |
. и мы получаем полезную таблицу, в которой есть номенклатура, период конкретного дня отчета, и дата максимального периода из таблицы регистра, по которому можно вытащить актуальную для этого дня цифирку.
5. Собственно и получаем актуальные остатки — связав с нашей таблицей максимальных периодов, таблицу остатков и оборотов по регистру (используем ту же виртуальную таблицу второй раз). Связь так же по номенклатуре и по колонке максимальных дат.
Вот такое вот решение задачи. Меня оно устроило — может еще кому пригодится.
Источник
