Расчет сальдо(кредитовое, дебетовое, свернутое)

Автор Сообщение
DenZ
Почетный форумщик

Зарегистрирован: 02.08.2007
Сообщения: 30
Откуда: BSH

Добавлено: 27.08.2007 10:56 Заголовок сообщения: Расчет сальдо(кредитовое, дебетовое, свернутое)
Добрый день,

Расчет сальдо(кредитовое, дебетовое, свернутое) по всем счетам, начинающимся на 60 (при условии, что длинна счета 12 символов):

select left(GL06001, 12) Account,sum(case when (GL06004 > 0 and GL06017 = ‘0’)or(GL06004 < 0 and GL06017 = ‘1’) then GL06004 else 0 end) DSaldo,-sum(case when (GL06004 < 0 and GL06017 = ‘0’)or(GL06004 > 0 and GL06017 = ‘1’) then GL06004 else 0 end) CSaldo,sum(GL06004) Saldofrom GL06V106where GL06001 like ‘60%’group by left(GL06001, 12)

В этом примере нужно быть аккуратным с полем GL06012. Нужно решить что делать со входящим сальдо (проводки типа "/") если оно есть. И отфильтровать проводки типа "|"

aav
Администратор
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

Добавлено: 27.08.2007 12:15 Заголовок сообщения: Расчет сальдо(кредитовое, дебетовое, свернутое)
По существу запроса:
желательно избегать выражения типа

Код:
where GL06001 like ‘60%’

лучше использовать взамен этого

Код:
where left(GL06001,2)=’60’

Последний раз редактировалось: aav (06.03.2016 11:07), всего редактировалось 1 раз

Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 28.08.2007 10:44 Заголовок сообщения: Демо-скрипт для вычисления дебета и кредита в ГК.
/* Для iScala (в Scala 5.1 длина счета определяется по-другому).
Позволяет задавать названия БД, код компании и год,
а также условия выборки строк проводок.
Считывает длину счета из параметров компании.
Не учитывает тип проводки GL06012.
Входящее сальдо отсекается по условию GL06003 <> 0.
CASE для дебета и кредита написан в демонстрационных целях.
На самом деле, первоначальный вариант CASE более понятен,
здесь просто немного другая техника как вариант. */

DECLARE @ScaDB VARCHAR(30)
DECLARE @SysDB VARCHAR(30)
DECLARE @Company CHAR(2)
DECLARE @FinYear CHAR(2)
DECLARE @sql_str VARCHAR(8000)
DECLARE @where_str VARCHAR(1000)

SET @ScaDB = ‘ScalaDB’
SET @SysDB = ‘ScaSystemDB’
SET @Company = ’01’
SET @FinYear = ’07’
SET @sql_str = SPACE(0)
SET @where_str = ‘ WHERE LEFT(GL06001, 2) = N»60» AND GL06003 <> 0 ‘

SET @sql_str = ‘
DECLARE @acc_len int

SELECT @acc_len = Length
FROM {SCADB}.dbo.ScaCompanySegment
WHERE CompanyCode = »{CC}»
AND SegmentID = 0

SELECT LEFT(GL06001, @acc_len) AS acc_str,
SUM( CASE GL06017
WHEN N»0» THEN SIGN(SIGN(GL06004) + 1) * GL06004
WHEN N»1» THEN -SIGN(SIGN(GL06004) — 1) * GL06004
ELSE 0.00
END) AS dr_amnt,
SUM( CASE GL06017
WHEN N»0» THEN SIGN(SIGN(GL06004) — 1) * GL06004
WHEN N»1» THEN -SIGN(SIGN(GL06004) + 1) * GL06004
ELSE 0.00
END) AS cr_amnt
FROM {SCADB}.dbo.GL06{CC}{YY} gl06 (NOLOCK)
{WHERE}
GROUP BY LEFT(GL06001, @acc_len)
ORDER BY LEFT(GL06001, @acc_len)

‘ — end @sql_str

SET @sql_str = REPLACE(@sql_str, ‘{SCADB}’, @ScaDB)
SET @sql_str = REPLACE(@sql_str, ‘{SYSDB}’, @SysDB)
SET @sql_str = REPLACE(@sql_str, ‘{CC}’, @Company)
SET @sql_str = REPLACE(@sql_str, ‘{YY}’, @FinYear)
SET @sql_str = REPLACE(@sql_str, ‘{WHERE}’, @where_str)

—PRINT @sql_str
EXEC( @sql_str )

GO

DenZ
Почетный форумщик

Зарегистрирован: 02.08.2007
Сообщения: 30
Откуда: BSH

Добавлено: 28.08.2007 15:55 Заголовок сообщения:
Отностельно типа проводки GL06012, я бы рекомендовал
отсекать проводки типа "|" и "a", а то результат будет неверным.
Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 28.08.2007 17:27 Заголовок сообщения:
У нас нет проводок типов ‘|’ и ‘a’, но если нужно строго отобрать только нужные типы проводок для расчетов, то в выражение WHERE нужно дописать что-то типа
and CHAR(GL06012) IN (‘/’,’0′,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′,’A’,’B’,’C’,’D’,’K’,’L’,’S’)

Для справки:
Обычные проводки: /,0,1,2,3,4,5,6,7,8,9,A,B,C,D,K,L,S
Сторно: 1
Проводки по отчетным уровням: \,a,c,d,e,f,g,h,j
Проводки по бюджетам: U,V,W,X,Y
Бюджет предыдущего года: Z
Разное: i,|,T

/ — Входящее сальдо
0 — Проводка ГК (прямой ввод)
1 — Сторно в ГК
2 — Периодическое распределение / переоценка валютных счетов
3 — Счет покупателя (КП)
4 — Платеж покупателя (КП)
5 — Счет поставщика (КЗ)
6 — Платеж поставщика (КЗ)
7 — Амортизация ОС (из модуля ОС)
8 — Автораспределение (периодические: 51-99)
9 — Проводка модуля Управление Проектами
A — Проводка модуля Зарплата
B — Проводка модуля Управление Запасами
C — Проводка модуля Долговые Обязательства
D — Проводка модуля Касса (ГК)
K — Переоценка ОС
L — Книги Выверки (ГК)
S — Проводка из другой компании при консолидации
T — Временные проводки по чекам в ГК (только в журнале ГК)
U — Бюджет 1
V — Бюджет 2
W — Бюджет 3
X — Бюджет 4
Y — Бюджет 5
Z — Бюджет предыдущего года
\ — Входящее сальдо по проводкам отчетного уровня
a — Проводка по отчетным уровням
c — Проводка по отчетным уровням Бюджет 1
d — Проводка по отчетным уровням Бюджет 2
e — Проводка по отчетным уровням Бюджет 3
f — Проводка по отчетным уровням Бюджет 4
g — Проводка по отчетным уровням Бюджет 5
h — Проводка по отчетным уровням Бюджет предыдущего года
i — Проводка закрывающего периода
j — Проводка по отчетным уровням закрывающего периода
| — Перенесенное входящее сальдо по валютным счетам

DenZ
Почетный форумщик

Зарегистрирован: 02.08.2007
Сообщения: 30
Откуда: BSH

Добавлено: 28.08.2007 19:58 Заголовок сообщения:
Вот спасибо, за полный список!
Dmitry Pestov
Заслуженный форумщик

Зарегистрирован: 21.06.2007
Сообщения: 94
Откуда: Москва, ApicoSoft

Добавлено: 28.08.2007 21:58 Заголовок сообщения: Re: Расчет сальдо(кредитовое, дебетовое, свернутое)

aav писал(а):
По существу запроса:
желательно избегать выражения типа

Код:
where GL06001 like ‘60%’

лучше использовать взамен этого

Код:
where left(GL06001,2)=’60’

В общем случае может это и верно, но для этого конкретного запроса не факт. Ведь в одном из индексов по этой таблице поле GL06001 стоит первым. Использование функции left сбивает оптимизатор и в плане запроса index scan, а при использовании like — index seek. Хотя тут просто недоработка в оптимизаторе, я пробовал на 2000м сервере.
_________________
Dmitry Pestov

Блог ScalaHelp.RU — практические вопросы использования Scala

Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 29.08.2007 09:16 Заголовок сообщения: Про использование LIKE
Насколько я себе представляю, LIKE может использовать индекс только в случае, если подстрока для поиска стоит в начале выражения, например, LIKE ‘ТЕКСТ%’. Еще лучше, если индекс будет состоять из одного этого поля. В общем случае, LIKE действительно не стоит использовать без крайней необходимости. На типичных объемах данных в таблицах проводок GL06 (десятки-сотни тысяч строк) при применении разных способов выборки разница между ними вообще может быть несущественной в том смысле, что любой способ будет применим.
Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 29.08.2007 12:10 Заголовок сообщения: Учетная строка
Кстати, об учетной строке (поле GL06001). Длина каждого из учетных измерений не более 12 символов, т.е. NVARCHAR(12) вполне достаточно для хранения (см. таблицу GL52). Длина первых 4-х измерений, включая счет, вместе не должна превышать 30 символов.
Dmitry Pestov
Заслуженный форумщик

Зарегистрирован: 21.06.2007
Сообщения: 94
Откуда: Москва, ApicoSoft

Добавлено: 29.08.2007 13:09 Заголовок сообщения: Re: Учетная строка

Jugulator писал(а):
Кстати, об учетной строке (поле GL06001). Длина каждого из учетных измерений не более 12 символов, т.е. NVARCHAR(12) вполне достаточно для хранения (см. таблицу GL52).

Ага точно, поправил.

Jugulator писал(а):
Длина первых 4-х измерений, включая счет, вместе не должна превышать 30 символов.

А вот это для 2.2 похоже не актуально.
_________________
Dmitry Pestov

Блог ScalaHelp.RU — практические вопросы использования Scala

vome
Народный форумщик

Зарегистрирован: 17.09.2004
Сообщения: 210
Откуда: Санкт-Петербург -> Москва

Добавлено: 29.08.2007 16:15 Заголовок сообщения: Re: Учетная строка

Dmitry Pestov писал(а):

Jugulator писал(а):
Длина первых 4-х измерений, включая счет, вместе не должна превышать 30 символов.

А вот это для 2.2 похоже не актуально.


К сожалению для 2.2 это тоже актуально.

aav
Администратор
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

Добавлено: 29.08.2007 16:24 Заголовок сообщения: Re: Учетная строка

vome писал(а):
К сожалению для 2.2 это тоже актуально.

Ты не поленился попытаться сделать больше? Very Happy

vome
Народный форумщик

Зарегистрирован: 17.09.2004
Сообщения: 210
Откуда: Санкт-Петербург -> Москва

Добавлено: 29.08.2007 17:25 Заголовок сообщения: Re: Учетная строка

aav писал(а):

vome писал(а):
К сожалению для 2.2 это тоже актуально.

Ты не поленился попытаться сделать больше? Very Happy

В начале этого года мы переходили на консолидированный план счетов и сначала сверстали его так, что длинна первых 4-х учетных измерений получилась 32, но при попытке ввода, Скала закричала, что она так не играет, пришлось пересмотреть длину первых 4-х.

Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 29.08.2007 17:28 Заголовок сообщения: Демо-скрипт для разбивки учетной строки.
/*
Демо-скрипт выполняется в контексте базы данных Scala 5.1 или iScala.
Показывает извлечение и использование информации об учетных измерениях
для анализа учетной строки.
*/
—USE ScalaDB
—GO

DECLARE @CC CHAR(2) /* Код компании */
DECLARE @YY CHAR(2) /* Финансовый год */
DECLARE @sql_str VARCHAR(8000) /* Строка запроса */

/* Укажите код компании и финансовый год. */
SET @CC = ’03’
SET @YY = ’04’

/* Это временная таблица для хранения информации о разбивке
учетной строки. Можно использовать одно представление (view)
вместо этой таблицы для всей базы данных. */
CREATE TABLE dbo.#ScaSegments (
CompanyCode CHAR(2) PRIMARY KEY,
Off0 int NOT NULL, Len0 int NOT NULL,
Off1 int NOT NULL, Len1 int NOT NULL,
Off2 int NOT NULL, Len2 int NOT NULL,
Off3 int NOT NULL, Len3 int NOT NULL,
Off4 int NOT NULL, Len4 int NOT NULL,
Off5 int NOT NULL, Len5 int NOT NULL,
Off6 int NOT NULL, Len6 int NOT NULL,
Off7 int NOT NULL, Len7 int NOT NULL,
Off8 int NOT NULL, Len8 int NOT NULL,
Off9 int NOT NULL, Len9 int NOT NULL
)

/* Проверка наличия таблицы ScaCompanySegment в базе,
это будет означать, что база iScala, иначе Scala 5.1. */
IF EXISTS(SELECT [name] FROM dbo.sysobjects
WHERE xtype = ‘U’ AND [name] = ‘ScaCompanySegment’)
BEGIN
INSERT INTO #ScaSegments (CompanyCode,
Off0, Len0, Off1, Len1, Off2, Len2, Off3, Len3, Off4, Len4
, Off5, Len5, Off6, Len6, Off7, Len7, Off8, Len8, Off9, Len9
) /* Заполняем таблицу для всех компаний iScala или делаем view. */
SELECT CompanyCode,
MAX((CASE WHEN SegmentID = 0 THEN Offset + 1 ELSE 0 END)) AS Off0,
MAX((CASE WHEN SegmentID = 0 THEN Length ELSE 0 END)) AS Len0,
MAX((CASE WHEN SegmentID = 1 THEN Offset + 1 ELSE 0 END)) AS Off1,
MAX((CASE WHEN SegmentID = 1 THEN Length ELSE 0 END)) AS Len1,
MAX((CASE WHEN SegmentID = 2 THEN Offset + 1 ELSE 0 END)) AS Off2,
MAX((CASE WHEN SegmentID = 2 THEN Length ELSE 0 END)) AS Len2,
MAX((CASE WHEN SegmentID = 3 THEN Offset + 1 ELSE 0 END)) AS Off3,
MAX((CASE WHEN SegmentID = 3 THEN Length ELSE 0 END)) AS Len3,
MAX((CASE WHEN SegmentID = 4 THEN Offset + 1 ELSE 0 END)) AS Off4,
MAX((CASE WHEN SegmentID = 4 THEN Length ELSE 0 END)) AS Len4,
MAX((CASE WHEN SegmentID = 5 THEN Offset + 1 ELSE 0 END)) AS Off5,
MAX((CASE WHEN SegmentID = 5 THEN Length ELSE 0 END)) AS Len5,
MAX((CASE WHEN SegmentID = 6 THEN Offset + 1 ELSE 0 END)) AS Off6,
MAX((CASE WHEN SegmentID = 6 THEN Length ELSE 0 END)) AS Len6,
MAX((CASE WHEN SegmentID = 7 THEN Offset + 1 ELSE 0 END)) AS Off7,
MAX((CASE WHEN SegmentID = 7 THEN Length ELSE 0 END)) AS Len7,
MAX((CASE WHEN SegmentID = 8 THEN Offset + 1 ELSE 0 END)) AS Off8,
MAX((CASE WHEN SegmentID = 8 THEN Length ELSE 0 END)) AS Len8,
MAX((CASE WHEN SegmentID = 9 THEN Offset + 1 ELSE 0 END)) AS Off9,
MAX((CASE WHEN SegmentID = 9 THEN Length ELSE 0 END)) AS Len9
FROM (
SELECT A.CompanyCode, A.SegmentID, ISNULL(SUM(B.Length), 0) AS Offset, A.Length
FROM iScalaDB.dbo.ScaCompanySegment A
LEFT OUTER JOIN iScalaDB.dbo.ScaCompanySegment B
ON A.CompanyCode = B.CompanyCode AND A.SegmentID > B.SegmentID
GROUP BY A.CompanyCode, A.SegmentID, A.Name, A.ShortName, A.Length
) ScaSegments
GROUP BY CompanyCode
END
ELSE /* Если база данных Scala 5.1. */
BEGIN
INSERT INTO #ScaSegments (CompanyCode,
Off0, Len0, Off1, Len1, Off2, Len2, Off3, Len3, Off4, Len4
, Off5, Len5, Off6, Len6, Off7, Len7, Off8, Len8, Off9, Len9
) /* Заполняем таблицу для всех компаний Scala 5.1 или делаем view. */
SELECT SY30001 AS CompanyCode,
1 AS Off0,
SY30002 AS Len0,
SY30002 + 1 AS Off1,
SY30004 AS Len1,
SY30002 + SY30004 + 1 AS Off2,
SY30006 AS Len2,
SY30002 + SY30004 + SY30006 + 1 AS Off3,
SY30008 AS Len3,
SY30002 + SY30004 + SY30006 + SY30008 + 1 AS Off4,
SY30010 AS Len4,
SY30002 + SY30004 + SY30006 + SY30008 + SY30010 + 1 AS Off5,
SY30012 AS Len5,
SY30002 + SY30004 + SY30006 + SY30008 + SY30010 + SY30012 + 1 AS Off6,
SY30014 AS Len6,
SY30002 + SY30004 + SY30006 + SY30008 + SY30010 + SY30012 + SY30014 + 1 AS Off7,
SY30016 AS Len7,
SY30002 + SY30004 + SY30006 + SY30008 + SY30010 + SY30012 + SY30014 + SY30016 + 1 AS Off8,
SY30018 AS Len8,
SY30002 + SY30004 + SY30006 + SY30008 + SY30010 + SY30012 + SY30014 + SY30016 + SY30018 + 1 AS Off9,
SY30020 AS Len9
FROM SY300000
END

/* Демонстрация примера использования таблицы #ScaSegments или view.
TOP 1000 используется для ограничения количества строк в примере. */
SET @sql_str = ‘
SELECT TOP 1000 GL06001,
RTRIM(SUBSTRING(GL06001, Off0, Len0)) AS Acc0,
RTRIM(SUBSTRING(GL06001, Off1, Len1)) AS Acc1,
RTRIM(SUBSTRING(GL06001, Off2, Len2)) AS Acc2,
RTRIM(SUBSTRING(GL06001, Off3, Len3)) AS Acc3,
RTRIM(SUBSTRING(GL06001, Off4, Len4)) AS Acc4,
RTRIM(SUBSTRING(GL06001, Off5, Len5)) AS Acc5,
RTRIM(SUBSTRING(GL06001, Off6, Len6)) AS Acc6,
RTRIM(SUBSTRING(GL06001, Off7, Len7)) AS Acc7,
RTRIM(SUBSTRING(GL06001, Off8, Len8)) AS Acc8,
RTRIM(SUBSTRING(GL06001, Off9, Len9)) AS Acc9
FROM GL06{CC}{YY}
INNER JOIN dbo.#ScaSegments
ON CompanyCode = »{CC}»
‘ — end @sql_str

SET @sql_str = REPLACE(@sql_str, ‘{CC}’, @CC)
SET @sql_str = REPLACE(@sql_str, ‘{YY}’, @YY)

EXEC( @sql_str )

DROP TABLE dbo.#ScaSegments

GO

Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 29.08.2007 17:35 Заголовок сообщения:
Небольшое исправление: "iScalaDB." нужно из скрипта убрать, оставить только "dbo.ScaCompanySegment", базу можно указать в начале скрипта с помощью USE. Соответственно, вместо просто SY300000 можно написать dbo.SY300000 для порядка.
DenZ
Почетный форумщик

Зарегистрирован: 02.08.2007
Сообщения: 30
Откуда: BSH

Добавлено: 11.02.2009 13:53 Заголовок сообщения: Re: Демо-скрипт для вычисления дебета и кредита в ГК.
Конструкцию

Jugulator писал(а):

SELECT LEFT(GL06001, @acc_len) AS acc_str,
SUM( CASE GL06017
WHEN N»0» THEN SIGN(SIGN(GL06004) + 1) * GL06004
WHEN N»1» THEN -SIGN(SIGN(GL06004) — 1) * GL06004
ELSE 0.00
END) AS dr_amnt,
SUM( CASE GL06017
WHEN N»0» THEN SIGN(SIGN(GL06004) — 1) * GL06004
WHEN N»1» THEN -SIGN(SIGN(GL06004) + 1) * GL06004
ELSE 0.00
END) AS cr_amnt

Можно немного упростить
SELECT LEFT(GL06001, @acc_len) AS acc_str,
SUM(CASE WHEN sign(GL06004)=(1-(GL06017)*2) THEN GL06004 ELSE 0 END) AS dr_amnt,
SUM(CASE WHEN sign(GL06004)<>(1-(GL06017)*2) THEN GL06004 ELSE 0 END) AS cr_amnt

Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 11.02.2009 22:26 Заголовок сообщения:

Цитата:
Конструкцию

Jugulator писал(а):

Можно немного упростить

С точки зрения стиля, наверное, правильнее будет писать только с использованием CASE и простых условий, когда в зависимости от GL06017 и SIGN(GL06004) выбирается или нет GL06004. В примере я написал выражение, которым сам не пользуюсь. В рабочей версии у меня CASE вообще нет, потому что писал давно, сейчас бы так не стал: с виду абсолютно непонятно, а выигрыш в производительности от силы в пару процентов.