Получение итогов (остатков) по периодам (день, документ) по регистрам остатков

Описание проблемы

    Как известно, 1С не может рассчитать остатки по регистру остатков, в случае если требуется получить такой остаток по предопределенным группировкам Документ, СтрокаДокумента. Также при рассчете остатков по периодам (Год, Квартал, Месяц, Неделя, День) 1С рассчитывает остатки не самым оптимальным образом, в результате при большом количестве движений рассчет может длится минуты, а то и десятки минут. Для решения этой проблемы предлагается сделать запрос средствами Transact-SQL, или с помощью метолов и обьектов ВК 1С++.
Рассмотрим каждій пример в отдельности

Реализация на Transact-SQL.

    Для начала рассмотрим вариант получения остатка на каждый документ. В приведенных примерах остатки получаются на начало каждого документа,
но при желании заменив условие "меньше" (<) на "меньше-равно" (<=) можно получать остатки по документ.
    Рассмотрим пример для периода "Документ"
SET NOCOUNT ON
-- Начало. Блок определения переменных для запроса
Declare @PERIOD_IN DateTime
Declare @PERIOD_IK DateTime
Declare @PERIODN Char(8)
Declare @PERIODK Char(23)
SET @PERIOD_IN = Convert(DateTime,'20060801',112) -- Начало периода для остатков
SET @PERIOD_IK = Convert(DateTime,'20060801',112) -- Конец периода для остатков
SET @PERIODN = '20060901' -- Начало периода получения итогов
SET @PERIODK = '20060930' -- Конец периода получения итогов
-- Конец. Блок определения переменных для запроса

-- Начало. Блок формирования оборотов по регистру
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#Tab_obor'))
DROP TABLE #Tab_obor

SELECT
TabR2.SP1061 AS Клиент,
TabJ.DATE_TIME_IDDOC AS Документ,
SUM(TabR2.SP7593*((TabR2.DEBKRED+1)%2)) AS Приход,
SUM(TabR2.SP7593*TabR2.DEBKRED) AS Рассход
INTO #Tab_obor
FROM
RA1060 AS TabR2 (NOLOCK)
INNER JOIN _1SJOURN As TabJ (NOLOCK) ON (TabR2.IDDOC = TabJ.IDDOC)

WHERE
TabJ.DATE_TIME_IDDOC >= @PERIODN
AND TabJ.DATE_TIME_IDDOC < @PERIODK
-- AND TabR2.SP1061 = ' KCO ' -- условие по клиенту
GROUP BY
TabR2.SP1061,
TabJ.DATE_TIME_IDDOC
-- Конец. Блок формирования оборотов по регистру

-- Начало. Блок формирования остатков
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#Tab_ost'))
DROP TABLE #Tab_ost

SELECT
TabR1.SP1061 AS Клиент,
CONVERT(char(8),DateADD(mm, 1,TabR1.PERIOD),112) AS Документ,
SUM(TabR1.SP7593) AS НачОст
INTO #Tab_ost
FROM
RG1060 AS TabR1 (NOLOCK)
WHERE
TabR1.PERIOD >= @PERIOD_IN
AND TabR1.PERIOD <= @PERIOD_IK
-- AND TabR1.SP1061 = -- Условие по клиенту
GROUP BY
TabR1.SP1061,
CONVERT(char(8),DateADD(mm, 1,TabR1.PERIOD),112)
HAVING
SUM(TabR1.SP7593) <> 0
-- Конец. Блок формирования остатков

-- Тест полученных таблиц
-- SELECT * FROM #Tab_ost
-- SELECT * FROM #Tab_obor
-- Начало. Блок формирования результирующего запроса по всем датам с остатками

IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#ResTab'))
DROP TABLE #ResTab

SELECT
TabOSN.Клиент AS Клиент,
TabOSN.Документ AS Документ,
SUM(TabOSN.Приход) AS Приход,
SUM(TabOSN.Рассход) AS Рассход,
(
(CASE WHEN
EXISTS
(SELECT * FROM #Tab_ost AS TabOst
WHERE
(TabOst.Клиент = TabOSN.Клиент)
AND
(LEFT(TabOst.Документ,6) = LEFT(TabOSN.Документ,6))
)
THEN
( SELECT SUM(isnull(TabOst.НачОст,0))
FROM
#Tab_ost AS TabOst
WHERE
(TabOst.Клиент = TabOSN.Клиент)
AND (LEFT(TabOst.Документ,6) = LEFT(TabOSN.Документ,6)))
ELSE 0
END
)
+
(CASE WHEN
EXISTS (SELECT * FROM #Tab_obor AS TabDop
WHERE
(TabDop.Клиент = TabOSN.Клиент)
AND
(TabDop.Документ < TabOSN.Документ)
AND
(LEFT(TabDop.Документ,6) = LEFT(TabOSN.Документ,6))
)
THEN
( SELECT SUM(isnull(TabDop.Приход-TabDop.Рассход,0))
FROM #Tab_obor AS TabDop
WHERE (TabDop.Клиент = TabOSN.Клиент)
AND (TabDop.Документ < TabOSN.Документ)
AND (LEFT(TabDop.Документ,6) = LEFT(TabOSN.Документ,6)))
ELSE
0
END)
) As ОСТ
INTO #ResTab
FROM #Tab_obor AS TabOSN
GROUP BY TabOSN.Клиент, TabOSN.Документ
ORDER BY TabOSN.Клиент, TabOSN.Документ
-- Конец. Блок формирования результирующего запроса по всем датам с остатками

SELECT * FROM #ResTab
-- Здесь может быть обработка оплученного результата
-- скажем для связи с спраовчником и сортировкой по наименованию


SELECT
SprKl.ID AS [Клиент $Справочник.Клиенты],
SprKl.DESCR AS Наименование,
T.Документ AS [Документ $Документ],
SUM(T.Приход) AS Приход,
SUM(T.Рассход) AS Рассход,
SUM(T.ОСТ) AS НачОст

FROM #ResTab AS T
INNER JOIN SC72 AS SprKl (NOLOCK) ON (T.Клиент = SprKl.ID)
GROUP BY
SprKl.ID,
SprKl.DESCR,
T.Документ
WITH ROLLUP
HAVING
SprKl.ID IS NOT NULL
AND SprKl.DESCR IS NOT NULL
ORDER BY
SprKl.DESCR,
T.Документ
SET NOCOUNT OFF

    Теперь рассмотрим пример для периода "День" с датами, по которым были движения
SET NOCOUNT ON
-- Начало. Блок определения переменных для запроса
Declare @PERIOD_IN DateTime
Declare @PERIOD_IK DateTime
Declare @PERIODN Char(8)
Declare @PERIODK Char(23)
SET @PERIOD_IN = Convert(DateTime,'20060801',112) -- Начало периода для остатков
SET @PERIOD_IK = Convert(DateTime,'20060801',112) -- Конец периода для остатков
SET @PERIODN = '20060901' -- Начало периода получения итогов
SET @PERIODK = '20060930' -- Конец периода получения итогов
-- Конец. Блок определения переменных для запроса

-- Начало. Блок формирования оборотов по регистру
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#Tab_obor'))
DROP TABLE #Tab_obor

SELECT
TabR2.SP1061 AS Клиент,
LEFT(TabJ.DATE_TIME_IDDOC,8) AS Документ,
SUM(TabR2.SP7593*((TabR2.DEBKRED+1)%2)) AS Приход,
SUM(TabR2.SP7593*TabR2.DEBKRED) AS Рассход
INTO #Tab_obor
FROM
RA1060 AS TabR2 (NOLOCK)
INNER JOIN _1SJOURN As TabJ (NOLOCK) ON (TabR2.IDDOC = TabJ.IDDOC)

WHERE
TabJ.DATE_TIME_IDDOC >= @PERIODN
AND TabJ.DATE_TIME_IDDOC < @PERIODK
-- AND TabR2.SP1061 = ' KCO ' -- условие по клиенту
GROUP BY
TabR2.SP1061,
LEFT(TabJ.DATE_TIME_IDDOC,8)
-- Конец. Блок формирования оборотов по регистру

-- Начало. Блок формирования остатков
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#Tab_ost'))
DROP TABLE #Tab_ost

SELECT
TabR1.SP1061 AS Клиент,
CONVERT(char(8),DateADD(mm, 1,TabR1.PERIOD),112) AS Документ,
SUM(TabR1.SP7593) AS НачОст
INTO #Tab_ost
FROM
RG1060 AS TabR1 (NOLOCK)
WHERE
TabR1.PERIOD >= @PERIOD_IN
AND TabR1.PERIOD <= @PERIOD_IK
-- AND TabR1.SP1061 = -- Условие по клиенту
GROUP BY
TabR1.SP1061,
CONVERT(char(8),DateADD(mm, 1,TabR1.PERIOD),112)
HAVING
SUM(TabR1.SP7593) <> 0
-- Конец. Блок формирования остатков

-- Тест полученных таблиц
-- SELECT * FROM #Tab_ost
-- SELECT * FROM #Tab_obor
-- Начало. Блок формирования результирующего запроса по всем датам с остатками

IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#ResTab'))
DROP TABLE #ResTab

SELECT
TabOSN.Клиент AS Клиент,
TabOSN.Документ AS День,
SUM(TabOSN.Приход) AS Приход,
SUM(TabOSN.Рассход) AS Рассход,
(
(CASE WHEN
EXISTS (SELECT * FROM #Tab_ost AS TabOst
WHERE
(TabOst.Клиент = TabOSN.Клиент)
AND (LEFT(TabOst.Документ,6) = LEFT(TabOSN.Документ,6))
) THEN
(SELECT SUM(isnull(TabOst.НачОст,0))
FROM
#Tab_ost AS TabOst
WHERE (TabOst.Клиент = TabOSN.Клиент)
AND (LEFT(TabOst.Документ,6) = LEFT(TabOSN.Документ,6)))
ELSE 0
END)+
(CASE WHEN
EXISTS (SELECT * FROM #Tab_obor AS TabDop
WHERE (TabDop.Клиент = TabOSN.Клиент)
AND (TabDop.Документ < TabOSN.Документ)
AND
(LEFT(TabDop.Документ,6) = LEFT(TabOSN.Документ,6))
) THEN
(SELECT SUM(isnull(TabDop.Приход-TabDop.Рассход,0))
FROM #Tab_obor AS TabDop
WHERE (TabDop.Клиент = TabOSN.Клиент)
AND
(TabDop.Документ < TabOSN.Документ)
AND (LEFT(TabDop.Документ,6) = LEFT(TabOSN.Документ,6)))
ELSE 0
END)
) As ОСТ
INTO #ResTab
FROM #Tab_obor AS TabOSN
GROUP BY TabOSN.Клиент, TabOSN.Документ
ORDER BY TabOSN.Клиент, TabOSN.Документ
-- Конец. Блок формирования результирующего запроса по всем датам с остатками
-- Здесь может быть обработка оплученного результата
-- скажем для связи с спраовчником и сортировкой по наименованию


SELECT
SprKl.ID AS [Клиент $Справочник.Клиенты],
SprKl.DESCR AS Наименование,
T.День AS [День $Дата],
SUM(T.Приход) AS Приход,
SUM(T.Рассход) AS Рассход,
SUM(T.ОСТ) AS НачОст

FROM #ResTab AS T
INNER JOIN SC72 AS SprKl (NOLOCK) ON (T.Клиент = SprKl.ID)
GROUP BY
SprKl.ID,
SprKl.DESCR,
T.День
WITH ROLLUP
HAVING
SprKl.ID IS NOT NULL
AND SprKl.DESCR IS NOT NULL
ORDER BY
SprKl.DESCR,
T.День
SET NOCOUNT OFF

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

Теперь по всем датам:
-- Начало. Блок определения переменных для запроса
Declare @PERIOD_IN DateTime
Declare @PERIOD_IK DateTime
Declare @PERIODN Char(8)
Declare @PERIODK Char(23)
SET @PERIOD_IN = Convert(DateTime,'20060801',112) -- Начало периода для остатков
SET @PERIOD_IK = Convert(DateTime,'20060801',112) -- Конец периода для остатков
SET @PERIODN = '20060901' -- Начало периода получения итогов
SET @PERIODK = '20060930' -- Конец периода получения итогов
-- Конец. Блок определения переменных для запроса

-- Начало. Блок фомрирования таблицы дат
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#PeriodTMP'))
DROP TABLE #PeriodTMP
CREATE TABLE #PeriodTMP
(
PeriodDate char(8) NOT NULL
)

declare @current datetime
declare @end_d datetime
set @current = Convert(dateTime, @PERIODN,112)
set @end_d = Convert(dateTime, @PERIODK,112)

while (select @current) <= @end_d
begin
insert into #PeriodTMP values (convert(char(8),@current,112))
set @current = dateadd(day, 1, @current)
end
-- Конец. Блок фомрирования таблицы дат
-- Начало. Блок формирования оборотов по регистру
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#TMP_obor'))
DROP TABLE #TMP_obor

SELECT
TabR2.SP1061 AS Клиент,
LEFT(TabJ.DATE_TIME_IDDOC,8) AS Документ,
SUM(TabR2.SP7593*((TabR2.DEBKRED+1)%2)) AS Приход,
SUM(TabR2.SP7593*TabR2.DEBKRED) AS Рассход
INTO #TMP_obor
FROM
RA1060 AS TabR2 (NOLOCK)
INNER JOIN _1SJOURN As TabJ (NOLOCK) ON (TabR2.IDDOC = TabJ.IDDOC)

WHERE
TabJ.DATE_TIME_IDDOC >= @PERIODN
AND TabJ.DATE_TIME_IDDOC < @PERIODK
-- AND TabR2.SP1061 = ' KCO ' -- условие по клиенту
GROUP BY
TabR2.SP1061,
LEFT(TabJ.DATE_TIME_IDDOC,8)
-- Конец. Блок формирования оборотов по регистру
-- Начало. Блок формирования оборотов за весь период (включая даты, по которым не было движений)
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#Tab_obor'))
DROP TABLE #Tab_obor

SELECT TMP.Клиент AS Клиент,
TMP.Документ AS Документ,
SUM(TMP.Приход) AS Приход,
SUM(TMP.Рассход) AS Рассход
INTO #Tab_obor
FROM
(
SELECT
tmp_t.Клиент,
tmp_t.Документ,
tmp_t.Приход AS Приход,
tmp_t.Рассход AS Рассход
FROM #TMP_obor AS tmp_t

UNION ALL
SELECT
DISTINCT tmp_t.Клиент AS Клиент,
TabPer.PeriodDate,
0,
0
FROM #PeriodTMP AS TabPer, #TMP_obor AS tmp_t
) AS TMP
GROUP BY
TMP.Клиент,
TMP.Документ
-- Конец. Блок формирования оборотов за весь период (включая даты, по которым не было движений)
-- Удаляем лишние таблицы
DROP TABLE #TMP_obor
DROP TABLE #PeriodTMP

-- Начало. Блок формирования остатков
IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#Tab_ost'))
DROP TABLE #Tab_ost

SELECT
TabR1.SP1061 AS Клиент,
CONVERT(char(8),DateADD(mm, 1,TabR1.PERIOD),112) AS Документ,
SUM(TabR1.SP7593) AS НачОст
INTO #Tab_ost
FROM
RG1060 AS TabR1 (NOLOCK)
WHERE
TabR1.PERIOD >= @PERIOD_IN
AND TabR1.PERIOD <= @PERIOD_IK
-- AND TabR1.SP1061 = -- Условие по клиенту
GROUP BY
TabR1.SP1061,
CONVERT(char(8),DateADD(mm, 1,TabR1.PERIOD),112)
HAVING
SUM(TabR1.SP7593) <> 0
-- Конец. Блок формирования остатков

-- Тест полученных таблиц
-- SELECT * FROM #Tab_ost
-- SELECT * FROM #Tab_obor
-- Начало. Блок формирования результирующего запроса по всем датам с остатками

IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#ResTab'))
DROP TABLE #ResTab

SELECT
TabOSN.Клиент AS Клиент,
TabOSN.Документ AS День,
SUM(TabOSN.Приход) AS Приход,
SUM(TabOSN.Рассход) AS Рассход,
(
(CASE WHEN
EXISTS (SELECT * FROM #Tab_ost AS TabOst
WHERE (TabOst.Клиент = TabOSN.Клиент)
AND
(LEFT(TabOst.Документ,6) = LEFT(TabOSN.Документ,6))
) THEN
(SELECT SUM(isnull(TabOst.НачОст,0))
FROM
#Tab_ost AS TabOst
WHERE (TabOst.Клиент = TabOSN.Клиент)
AND
(LEFT(TabOst.Документ,6) = LEFT(TabOSN.Документ,6)))
ELSE 0
END)+
(CASE WHEN
EXISTS
(SELECT * FROM #Tab_obor AS TabDop
WHERE (TabDop.Клиент = TabOSN.Клиент)
AND (TabDop.Документ < TabOSN.Документ)
AND
(LEFT(TabDop.Документ,6) = LEFT(TabOSN.Документ,6))
) THEN
(SELECT SUM(isnull(TabDop.Приход-TabDop.Рассход,0))
FROM
#Tab_obor AS TabDop
WHERE (TabDop.Клиент = TabOSN.Клиент)
AND
(TabDop.Документ < TabOSN.Документ)
AND (LEFT(TabDop.Документ,6) = LEFT(TabOSN.Документ,6)))
ELSE 0
END)
) As ОСТ
INTO #ResTab
FROM #Tab_obor AS TabOSN
GROUP BY TabOSN.Клиент, TabOSN.Документ
ORDER BY TabOSN.Клиент, TabOSN.Документ
-- Конец. Блок формирования результирующего запроса по всем датам с остатками
-- Здесь может быть обработка оплученного результата скажем для связи с спраовчником и сортировкой по наименованию

SELECT
SprKl.ID AS [Клиент $Справочник.Клиенты],
SprKl.DESCR AS Наименование,
T.День AS [День $Дата],
SUM(T.Приход) AS Приход,
SUM(T.Рассход) AS Рассход,
SUM(T.ОСТ) AS НачОст

FROM #ResTab AS T
INNER JOIN SC72 AS SprKl (NOLOCK) ON (T.Клиент = SprKl.ID)
GROUP BY
SprKl.ID,
SprKl.DESCR,
T.День
WITH ROLLUP
HAVING
SprKl.ID IS NOT NULL
AND SprKl.DESCR IS NOT NULL
ORDER BY
SprKl.DESCR,
T.День
Теперь рассмотрим пример при использовании методов 1С++, в частности виртуальной таблицы ОстаткиИОбороты.

© pvase 2006