Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
Riken
Member

Откуда:
Сообщений: 16
Уважаемые, я только учусь, можно сказать полный нуб, так что сильно не пинайте ))

Для тренировки TSQL решил сделать некое подобие системы по учету операций с деньгами (Black book) на примере сельской корпорации )).

Есть три таблицы:
1. accounts - счета \ места хранения денег (Р\С, пластик, т.п.)
2. doctypes - типы документов (Приход, Расход, Списание, т.п.)
3. paydocs - собственно документы по платежам (операциям по счетам)

PRINT (N'Создать таблицу "dbo.doctypes"')
GO
CREATE TABLE dbo.doctypes (
  id int IDENTITY,
  name nvarchar(50) NULL,
  CONSTRAINT PK_doctypes PRIMARY KEY CLUSTERED (id)
)
ON [PRIMARY]
GO

--
-- Создать таблицу "dbo.accounts"
--
PRINT (N'Создать таблицу "dbo.accounts"')
GO
CREATE TABLE dbo.accounts (
  id int IDENTITY,
  name nvarchar(50) NULL,
  CONSTRAINT PK_accounts PRIMARY KEY CLUSTERED (id)
)
ON [PRIMARY]
GO

--
-- Создать таблицу "dbo.paydocs"
--
PRINT (N'Создать таблицу "dbo.paydocs"')
GO
CREATE TABLE dbo.paydocs (
  id int IDENTITY,
  date datetime NULL CONSTRAINT DF_paydocs_date DEFAULT (getdate()),
  doctype int NULL,
  opercode int NULL,
  from_account int NULL,
  to_account int NULL,
  amount decimal(18, 2) NULL,
  CONSTRAINT PK_paydocs PRIMARY KEY CLUSTERED (id)
)
ON [PRIMARY]
GO
-- 
-- Вывод данных для таблицы accounts
--
SET IDENTITY_INSERT dbo.accounts ON
GO
INSERT dbo.accounts(id, name) VALUES (1, N'Карта Сбербанка')
INSERT dbo.accounts(id, name) VALUES (2, N'Р\С ИП Иванов И.И.')
INSERT dbo.accounts(id, name) VALUES (3, N'Р\С ООО Морковка')
GO
SET IDENTITY_INSERT dbo.accounts OFF
GO
-- 
-- Вывод данных для таблицы doctypes
--
SET IDENTITY_INSERT dbo.doctypes ON
GO
INSERT dbo.doctypes(id, name) VALUES (1, N'РАСХОД')
INSERT dbo.doctypes(id, name) VALUES (2, N'ПРИХОД')
INSERT dbo.doctypes(id, name) VALUES (3, N'ПЕРЕМЕЩЕНИЕ')
INSERT dbo.doctypes(id, name) VALUES (4, N'СПИСАНИЕ')
INSERT dbo.doctypes(id, name) VALUES (5, N'ВВОД ОСТАТКОВ')
GO
SET IDENTITY_INSERT dbo.doctypes OFF
GO
-- 
-- Вывод данных для таблицы paydocs
--
SET IDENTITY_INSERT dbo.paydocs ON
GO
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (14, '2015-08-01 23:42:59.527', 2, 1, NULL, 1, 1000.00)
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (15, '2015-08-01 23:43:26.253', 1, -1, 1, NULL, 990.00)
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (16, '2015-08-02 23:51:48.220', 2, 1, NULL, 3, 58700.00)
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (17, '2015-08-02 23:52:24.490', 1, -1, 3, NULL, 31200.00)
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (18, '2015-08-03 23:58:44.417', 1, -1, 3, NULL, 7650.00)
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (19, '2015-08-04 23:59:13.583', 4, -1, 3, NULL, 50.00)
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (20, '2015-08-05 23:59:39.027', 2, 1, NULL, 2, 34370.00)
INSERT dbo.paydocs(id, date, doctype, opercode, from_account, to_account, amount) 
VALUES (21, '2015-08-06 00:00:20.833', 1, -1, 2, NULL, 29100.00)
GO
SET IDENTITY_INSERT dbo.paydocs OFF
GO


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

SELECT a.name, ISNULL(SUM(pd.amount * pd.opercode), 0) AS Ostatok
  FROM
  paydocs pd RIGHT JOIN accounts a ON pd.to_account = a.id OR pd.from_account = a.id
  GROUP BY a.name


Еще хотел бы спросить, как быть с перемещением денег, а точнее, с минусом или плюсом сохранять эту строку?
При Расходе в Opercode я пишу "-1", при Приходе в Opercode пишу "1", а что писать при перемещение, ведь в этой операции есть Расход и Приход в одной строке?
14 авг 15, 12:37    [18017418]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Riken,

RIGHT JOIN глаза режет
14 авг 15, 12:43    [18017448]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Riken
Еще хотел бы спросить, как быть с перемещением денег, а точнее, с минусом или плюсом сохранять эту строку?
При Расходе в Opercode я пишу "-1", при Приходе в Opercode пишу "1", а что писать при перемещение, ведь в этой операции есть Расход и Приход в одной строке?


Операция перемещения в нормальных системах рождает две атомарные операции приход и расход.
14 авг 15, 12:45    [18017455]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
Riken
Member

Откуда:
Сообщений: 16
WarAnt,

автор
Операция перемещения в нормальных системах рождает две атомарные операции приход и расход.


Я уже дошел до этого, хотел при перемещение сохранять Расход со счета откуда деньги уходят и Приход на счет куда деньги приходят.
Но(!), как при моей структуре БД вывести список документов, где doctype = 'ПЕРЕМЕЩЕНИЕ', ведь у нас 2 строки (2 документа и doctype'а 2: Приход и Расход) ...
14 авг 15, 12:50    [18017490]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Riken
Но(!), как при моей структуре БД вывести список документов, где doctype = 'ПЕРЕМЕЩЕНИЕ', ведь у нас 2 строки (2 документа и doctype'а 2: Приход и Расход) ...


никак, у Вас неправильная структура БД не подразумевающая такую двух шаговую операцию как перемещение.
То что Вы добавили в таблицу тип ПЕРЕМЕЩЕНИЕ не делает структуру автоматически правильной:)
Нет, можно конечно сделать костыль в selecte что при наличии обоих заполненных полей from-to клонировать строку с перевертыванием суммы и получателя, но это именно костыль.
14 авг 15, 15:39    [18018609]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
Riken
Member

Откуда:
Сообщений: 16
WarAnt, я немного изменил структуру paydocs для хранения перемещения с расходом и приходом - в разделе
Проектирование БД выложил.
17 авг 15, 05:04    [18026733]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Riken,

select
a.name,
sum(pd.amount * case when pd.opercode = +1 then +1
					 when pd.opercode = -1 then -1
					 when pd.opercode =  4 and pd.from_account = a.id then -1
					 when pd.opercode =  4 and pd.to_account   = a.id then +1
					 end) Rest
from Accounts a with(nolock)
left join paydocs pd with(nolock) on pd.to_account = a.id OR pd.from_account = a.id
group by a.Name


1.Приход, расход и перемещение - это правильно.
2.Нужна еще табличка с остатками на дату, чтобы каждый раз не бегать по всем документам.

create table Balance
(
IDAccount int,
Data datetime,
Rest money,
primary key (IDAccount, Data)
)

И так же нужен триггер на табличку paydoc, который будет обрабатывать документы и менять значения в табличке Balance.
17 авг 15, 12:46    [18028305]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Riken,

Смотри тут -
17 авг 15, 15:45    [18029277]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
Riken
Member

Откуда:
Сообщений: 16
Jaffar, большое спасибо. Тоже думал над хранением остатков, но не трогал этот вопрос, так как не был уверен в структуре БД.
17 авг 15, 20:04    [18030578]     Ответить | Цитировать Сообщить модератору
 Re: Расчет остатков денег с группировкой по счетам (проверьте пож.)  [new]
Riken
Member

Откуда:
Сообщений: 16
gandjustas, ))) Вчера тут в блогах нарыл это видео, потом из видео пошел статью на РСДН изучать. В любом случае спасибо. Разбираюсь с вопросом.
17 авг 15, 20:06    [18030588]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить