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

Откуда: Гималай
Сообщений: 2101
Приветствую всех.
Поиском подходящую тему не смог найти.
Опишу проблему:

Есть таблица касс:
TblRefCash
ID
Cash


Есть таблица входящих остатков касс за кажды месяц:
TblRefCashInc
Cash_ID
Period
Inc

В данной таблице поле Cash_ID указывает на кассу и Period хранит значения в виде: 2011-04-01, 2011-05-01 и т.д., т.е. первые числа каждого месяца и Inc - входящий баланс кассы за определенный месяц

Также есть таблица кассовых документов:
TblDocCash
Cash_ID
DT
Contragent_ID
CashOperation
CashSum

Здесь Cash_ID указывает на кассу, DT - дата операции
В данный момент при добавлении записи в таблицу TblDocCash в триггере добавил вызов хранимки, которая пересчитывает входящие остатки в таблице TblRefCashInc.

Код хранимки:
ALTER PROCEDURE [dbo].[SP_CalcInc] (
	@Cash_ID bigint,
	@DT datetime
) AS
BEGIN
	SET NOCOUNT ON;
	DECLARE
		@PeriodStart datetime, @NextPeriod datetime,
		@Exist bit, @Inc money
	SET @Exist=0
	SET @PeriodStart=CAST(CONVERT(varchar,DATEADD(day,1-DAY(@DT),@DT),112)+' 00:00:00' AS datetime)
	SET @NextPeriod=DATEADD(m,1,@PeriodStart)
	IF EXISTS (SELECT * FROM TblRefCashInc WHERE Cash_ID=@Cash_ID AND Period=@NextPeriod)
		SET @Exist=1
	WHILE @Exist=1 BEGIN
		SET @Inc=0
		SELECT @Inc=Inc FROM TblRefCashInc WHERE Cash_ID=@Cash_ID AND Period=@PeriodStart
		SET @Inc=(SELECT ISNULL(SUM(CashSum),0) FROM TblDocCash WHERE Cash_ID=@Cash_ID AND DT>=@PeriodStart AND DT<@NextPeriod AND CashOperation=1)
		SET @Inc=@Inc-(SELECT ISNULL(SUM(CashSum),0) FROM TblDocCash WHERE Cash_ID=@Cash_ID AND DT>=@PeriodStart AND DT<@NextPeriod AND CashOperation=2)
		SET @Inc=@Inc-(SELECT ISNULL(SUM(CashSum),0) FROM TblDocCash WHERE Cash_ID=@Cash_ID AND DT>=@PeriodStart AND DT<@NextPeriod AND CashOperation=3)
		SET @Inc=@Inc+(SELECT ISNULL(SUM(CashSum),0) FROM TblDocCash WHERE Dst_Cash_ID=@Cash_ID AND DT>=@PeriodStart AND DT<@NextPeriod AND CashOperation=3)
		
		UPDATE TblRefCashInc SET Inc=@Inc WHERE Cash_ID=@Cash_ID AND Period=@NextPeriod
		
		SET @PeriodStart=@NextPeriod
		SET @NextPeriod=DATEADD(m,1,@PeriodStart)
		IF NOT EXISTS (SELECT * FROM TblRefCashInc WHERE Cash_ID=@Cash_ID AND Period=@NextPeriod)
			SET @Exist=0
	END
END
Можно ли это упростить? Без цикла к примеру?
Спасибо за внимание.
8 май 11, 20:39    [10624070]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
davim
Member

Откуда:
Сообщений: 11
Зачем здесь вообще цикл? Все делается одним апдейтом.

declare @tblRefCashInc table(Cash_ID int, Period datetime, Inc numeric(19,8))
declare @tblDocCash table(Cash_ID int, DT datetime, CashOperation int, CashSum numeric(19,8))

declare @StartPeriod datetime
	, @EndPeriod datetime

update rci
set Inc = sq.Inc
from @tblRefCashInc rci
	inner join
	(
		select dc.Cash_ID
			, dc.DT as Period
			, sum(
				case 
					when CashOperation in (1,4) then dc.CashSum 
					when CashOperation in (2,3) then -dc.CashSum
				end) as Inc				
		from @tblDocCash dc
		where dc.DT between @StartPeriod and @EndPeriod
		group by dc.DT, dc.Cash_ID
	) sq on sq.Cash_ID = rci.Cash_ID 
		and sq.Period = rci.Period
8 май 11, 21:18    [10624213]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
davim,

спасибо за направление, но данный вариант не совсем подойдет
потому как, как я понял, в данном примере не учитывается остаток из предыдущего месяца
т.е. остатки должны накапливаться и передаваться к следующим месяцам, если они есть
например:
CREATE TABLE TblRefCash (
		ID bigint,
		Cash nvarchar(200)
	)
CREATE TABLE TblRefCashInc (
		Cash_ID bigint,
		Period datetime,
		Inc money
	)
CREATE TABLE TblDocCash (
		ID bigint,
		Cash_ID bigint,
		DT datetime,
		CashOperation tinyint, --1 - приход, 2 - расход
		CashSum money
	)

DECLARE
	@Cash_ID bigint, @DT datetime, @CashOperation tinyint

INSERT INTO TblRefCash SELECT 1, N'Касса'

INSERT INTO TblRefCashInc SELECT 1, '2011-04-01', 0
INSERT INTO TblRefCashInc SELECT 1, '2011-05-01', 0
INSERT INTO TblRefCashInc SELECT 1, '2011-06-01', 0
INSERT INTO TblRefCashInc SELECT 1, '2011-07-01', 0

Есть одна касса и 4 остатков этой кассы за апрель, май, июнь и июль месяцы.
И я думаю повесить триггер на таблицу TblDocCash, который и переносит остатки на следующие месяцы
К примеру если есть оплата за апрель месяц, не обязательно первое число, вообще с 01.04 по 30.04 то остаток записи за май месяц и июнь и июль, соответственно обновляются с учетом добавленных данных
Если же запись за май месяц, остатки обновляются за июнь и июль.
Из-за этого и сделал цикл, в котором собираются остатки и переносятся на следующий месяц
8 май 11, 21:53    [10624307]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
davim
Member

Откуда:
Сообщений: 11
orunbek, тогда в моем скрипте надо просто прибавить к sum(...) остаток за период dateadd(month,-1,@StartPeriod)
declare @tblRefCashInc table(Cash_ID int, Period datetime, Inc numeric(19,8))
declare @tblDocCash table(Cash_ID int, DT datetime, CashOperation int, CashSum numeric(19,8))

declare @StartPeriod datetime
	, @EndPeriod datetime
	, @PrevPeriod datetime

update rci
set Inc = sq.Inc
from @tblRefCashInc rci
	inner join
	(
		select dc.Cash_ID
			, dc.DT as Period
			, sum(
				case 
					when CashOperation in (1,4) then dc.CashSum 
					when CashOperation in (2,3) then -dc.CashSum
				end) + (select Inc from @tblRefCashInc where Cash_ID = dc.Cash_ID and Period = @PrevPeriod) as Inc				
		from @tblDocCash dc
		where dc.DT between @StartPeriod and @EndPeriod
		group by dc.DT, dc.Cash_ID
	) sq on sq.Cash_ID = rci.Cash_ID 
		and sq.Period = rci.Period
8 май 11, 22:05    [10624347]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
iljy
Member

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

вы трудотерапию чтоли к серверу применяете? Зачем в триггере каждый раз пересчитывать все? Учитывайте только изменения!
+
drop table TblRefCash
drop table TblRefCashInc
drop table TblDocCash
go
CREATE TABLE TblRefCash (
		ID bigint,
		Cash nvarchar(200)
	)
CREATE TABLE TblRefCashInc (
		Cash_ID bigint,
		Period datetime,
		Inc money
	)
CREATE TABLE TblDocCash (
		ID bigint,
		Cash_ID bigint,
		DT datetime,
		CashOperation tinyint, --1 - приход, 2 - расход
		CashSum money
	)

DECLARE
	@Cash_ID bigint, @DT datetime, @CashOperation tinyint

INSERT INTO TblRefCash SELECT 1, N'Касса'

INSERT INTO TblRefCashInc SELECT 1, '20110401', 0
INSERT INTO TblRefCashInc SELECT 1, '20110501', 0
INSERT INTO TblRefCashInc SELECT 1, '20110601', 0
INSERT INTO TblRefCashInc SELECT 1, '20110701', 0
go
create trigger TR_CalcInc on TblDocCash for insert,update,delete
as
	update TblRefCashInc
	set Inc = isnull(Inc,0) + s
	from(
		select ti.Cash_ID id, ti.Period p, SUM(CashSum) s 
		from TblRefCashInc ti
			join
		(
			select Cash_ID, DT, CashSum * (1-(CashOperation - 1) * 2) CashSum from inserted
				union all
			select Cash_ID, DT, CashSum * ((CashOperation - 1) * 2 - 1) from deleted
		)t on ti.Cash_ID = t.Cash_ID and ti.Period > t.DT
		group by ti.Cash_ID, ti.Period
	) t
	where Cash_ID = id and Period = p and s !=0
GO
insert TblDocCash values
(1,1,'20110325', 1,100),
(2,1,'20110326', 2,150),
(3,1,'20110425', 1,10),
(4,1,'20110425', 1,2),
(5,1,'20110625', 1,100)

select * from TblRefCashInc

delete TblDocCash
where ID in (1,3)

select * from TblRefCashInc
8 май 11, 22:31    [10624426]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
orunbek
Поиском подходящую тему не смог найти.
А вообще искали? (20 тем всего навсего и все по делу)

orunbek
Можно ли это упростить? Без цикла к примеру?
Вообще не писать код, пусть само считает: 10557715

Советую обдумать вариант с индексированным представлением (если конечно уверены в прямоте своих рук).
9 май 11, 00:41    [10624793]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
davim,

спасибо, попробую вариант предложенный iljy скомбинировать с вашим вариантом

Mnior,
спасибо за конструктивные замечания и за комплименты по поводу прямоты моих рук
Я не уверен что они прямые и не уверен вообще есть ли они ;-)

iljy,
спасибо, за неимением опыта и применяю трудотерапию ;-)
чуть изменил, разбил на отдельные триггеры, потому как есть еще другие коды работающие в триггере
к примеру на INSERT сделал следующим образом
CREATE TRIGGER [dbo].[TR_TblDocCash_INSERT]
   ON  [dbo].[TblDocCash]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	UPDATE
		TblRefCashInc
	SET
		Inc=ISNULL(Inc,0)+S
	FROM (
		SELECT
			ci.Cash_ID AS C, ci.Period AS P, SUM(CashSum) AS S
		FROM
			TblRefCashInc ci INNER JOIN (
				SELECT Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=1 GROUP BY Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)
				UNION ALL
				SELECT Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, -SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=2 GROUP BY Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)
				UNION ALL
				SELECT Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, -SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=3 GROUP BY Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)
				UNION ALL
				SELECT Dst_Cash_ID AS Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=3 GROUP BY Dst_Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)
			) dc ON ci.Cash_ID=dc.Cash_ID AND ci.Period>dc.DT
		GROUP BY
			ci.Cash_ID, ci.Period
	) tci
	WHERE
		Cash_ID=C AND Period=P AND S<>0
END
GO
В вопросе не указывал описание операции CashOperation=3, это перемещение денег из одно кассы в другую: с Cash_ID в Dst_Cash_ID
И пример:
delete TblRefCashInc
delete TblDocCash

INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-03-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-04-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-05-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-06-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-07-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-08-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 2, '2011-05-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 2, '2011-06-01', 0

insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 1, 1, '2011-04-01', 1, 100, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 2, 1, '2011-04-08', 1, 10, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 3, 1, '2011-04-20', 2, 50, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 4, 1, '2011-05-10', 2, 10, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 5, 2, '2011-06-08', 1, 200, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 6, 1, '2011-06-01', 3, 1, 2

update TblDocCash set CashOperation=2 where ID=2
delete TblDocCash where ID=6

select * from TblRefCashInc

Спасибо
10 май 11, 12:52    [10628608]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Млин, забыл спросить
В данном методе обновляются все записи из TblRefCashInc касающиеся Cash_ID из inserted не зависимо от даты
[b][color=red]INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-03-01', 10[/color][/b]INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-04-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-05-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-06-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-07-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-08-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 2, '2011-05-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 2, '2011-06-01', 0

insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 1, 1, '2011-04-01', 1, 100, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 2, 1, '2011-04-08', 1, 10, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 3, 1, '2011-04-20', 2, 50, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 4, 1, '2011-05-10', 2, 10, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 5, 2, '2011-06-08', 1, 200, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 6, 1, '2011-06-01', 3, 1, 2

к примеру есть записи по остаткам кассы Cash_ID=1:
Cash_ID Period Inc
1 2011-03-01 10


за март месяц, но нет записей в TblDocCash за март месяц, но при срабатывании триггера запись кассы с ID 1 сбрасывается в 0
т.е. после добавления записи в TblDocCash срабатывает триггер и сбрасывает запись за март месяц в
Cash_ID Period Inc
1 2011-03-01 0
10 май 11, 13:26    [10628853]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
iljy
Member

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

вы сейчас о каком методе говорите? В моем триггере ничего подобного не происходит, в вашем (хотя вы его изрядно переусложнили) тоже.
10 май 11, 13:40    [10628955]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek,

вы сейчас о каком методе говорите? В моем триггере ничего подобного не происходит, в вашем (хотя вы его изрядно переусложнили) тоже.


в моем триггере я изменил
select Cash_ID, DT, CashSum * (1-(CashOperation - 1) * 2) CashSum from inserted
union all
select Cash_ID, DT, CashSum * ((CashOperation - 1) * 2 - 1) from deleted

на
SELECT Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=1 GROUP BY Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)
				UNION ALL
				SELECT Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, -SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=2 GROUP BY Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)
				UNION ALL
				SELECT Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, -SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=3 GROUP BY Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)
				UNION ALL
				SELECT Dst_Cash_ID AS Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime) AS DT, SUM(CashSum) AS CashSum FROM inserted WHERE CashOperation=3 GROUP BY Dst_Cash_ID, CAST(CONVERT(varchar,DT,112) AS datetime)

я переделал в
CREATE TRIGGER [dbo].[TR_TblDocCash_INSERT]
   ON  [dbo].[TblDocCash]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	UPDATE
		TblRefCashInc
	SET
		Inc=ISNULL(Inc,0)+S
	FROM (
		SELECT
			ci.Cash_ID AS C, ci.Period AS P, SUM(CashSum) AS S
		FROM
			TblRefCashInc ci INNER JOIN (
				SELECT Cash_ID, DT, CashSum AS CashSum FROM inserted WHERE CashOperation=1
				UNION ALL
				SELECT Cash_ID, DT, -CashSum AS CashSum FROM inserted WHERE CashOperation=2
				UNION ALL
				SELECT Cash_ID, DT, -CashSum AS CashSum FROM inserted WHERE CashOperation=3
				UNION ALL
				SELECT Dst_Cash_ID AS Cash_ID, DT, CashSum FROM inserted WHERE CashOperation=3
			) dc ON ci.Cash_ID=dc.Cash_ID AND ci.Period>dc.DT
		GROUP BY
			ci.Cash_ID, ci.Period
	) tci
	WHERE
		Cash_ID=C AND Period=P AND S<>0
END

а да, он реально этого не делает, просто я стормозил и "не заметил" эту инструкцию в тестовом скрипте:
update TblRefCashInc set Inc=0
10 май 11, 14:57    [10629707]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
iljy
Member

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

можно еще проще
SELECT Cash_ID, DT, CashSum * (1-2*sign(CashOperation-1)) AS CashSum 
FROM inserted WHERE CashOperation in (1,2,3)
	UNION ALL
SELECT Dst_Cash_ID AS Cash_ID, DT, CashSum FROM inserted WHERE CashOperation=3
10 май 11, 15:02    [10629748]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
После слова UPDATE укажите alias таблицы (ci), а не заворачивайте всё в подзапрос.
10 май 11, 15:20    [10629914]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
UPDATE (Transact-SQL) | Указание целевых объектов, отличных от стандартных таблиц
10 май 11, 15:22    [10629938]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy,

да, прекрасный вариант
вот только думаю по поводу того, чтобы при обновлении еще накапливать остатки с предыдущих записей
т.е. в этом скрипте:
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-03-01', 1
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-04-01', 10
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-05-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-06-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-07-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 1, '2011-08-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 2, '2011-05-01', 0
INSERT INTO TblRefCashInc (Cash_ID, Period, Inc)
SELECT 2, '2011-06-01', 0

insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 1, 1, '2011-04-01', 1, 100, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 2, 1, '2011-04-08', 1, 10, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 3, 1, '2011-04-20', 2, 50, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 4, 1, '2011-05-10', 2, 10, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 5, 2, '2011-06-08', 1, 200, -1
insert into TblDocCash (ID, Cash_ID, DT, CashOperation, CashSum, Dst_Cash_ID)
select 6, 1, '2011-06-01', 3, 1, 2

остаток в 10 рублей с апреля месяца добавился к остатку за май месяц
попробовал сделать:
UPDATE
	TblRefCashInc+ISNULL((SELECT Inc FROM TblRefCashInc WHERE Cash_ID=C AND Period=DATEADD(m,-1,P)),0)
SET
	Inc=ISNULL(Inc,0)+S

"белиберда" выходит :-)
ммм... может быть до UPDATE'а в табличную переменную взять остатки за предыдущие месяцы? и уже дальше использовать в основном UPDATE'е?


Mnior,
...
UPDATE
	TblRefCashInc
SET
	Inc=ISNULL(Inc,0)+S
...

вот эту инструкцию имеете в виду? Но если я правильно понял
UPDATE
	TblRefCashInc
SET
	Inc=ISNULL(Inc,0)+S
FROM (
	SELECT
		ci.Cash_ID AS C, ci.Period AS P, SUM(CashSum) AS S
	FROM
		TblRefCashInc ci INNER JOIN (
			SELECT Cash_ID, DT, CashSum*(1-2*SIGN(CashOperation-1)) AS CashSum FROM inserted
			UNION ALL
			SELECT Dst_Cash_ID AS Cash_ID, DT, CashSum FROM inserted WHERE CashOperation=3
		) dc ON ci.Cash_ID=dc.Cash_ID AND ci.Period>dc.DT
	GROUP BY
		ci.Cash_ID, ci.Period
) tci
WHERE
	Cash_ID=C AND Period=P AND S<>0

1. "Собираются" все операции за прошлый месяц
SELECT Cash_ID, DT, CashSum*(1-2*SIGN(CashOperation-1)) AS CashSum FROM inserted
UNION ALL
SELECT Dst_Cash_ID AS Cash_ID, DT, CashSum FROM inserted WHERE CashOperation=3

2. Группируются все кассы соответствующие собранным операциям и плюс суммируются все операции
SELECT
	ci.Cash_ID AS C, ci.Period AS P, SUM(CashSum) AS S
FROM
	TblRefCashInc ci INNER JOIN (
	...
) dc ON ci.Cash_ID=dc.Cash_ID AND ci.Period>dc.DT
GROUP BY
	ci.Cash_ID, ci.Period

3. И затем все просуммированные и сгруппированные по периоду и кассе операции отражаются в таблице TblRefCashInc
UPDATE
	TblRefCashInc
SET
	Inc=ISNULL(Inc,0)+S
FROM (
) tci
WHERE
	Cash_ID=C AND Period=P AND S<>0
Или я ошибаюсь?
10 май 11, 16:44    [10630634]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
iljy
Member

Откуда:
Сообщений: 8711
orunbek
iljy,

да, прекрасный вариант
вот только думаю по поводу того, чтобы при обновлении еще накапливать остатки с предыдущих записей

Так сразу записывайте в таблицу запись с правильными остатками. По логике ведь должно быть, что если в апреле не было операций, то остаток на 1 мая равен остатку на 1 апреля. А у вас они разные! Соответственно если у вас в таблицу TblRefCashInc добавляются записи, то они должны быть логически корректны. А триггер эту корректность может только поддерживать.
10 май 11, 17:27    [10630937]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy,

тоже верно, ну и в крайнем случае реализую отдельную хранимку, которая по тому же принципу что и триггер, только уже на таблицах TblDocCash перепроверяет остатки за все месяцы
Пока что попробую по этому принципу реализовать
Большое спасибо всем
10 май 11, 17:33    [10630974]     Ответить | Цитировать Сообщить модератору
 Re: Остатки по месяцам  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
  • Имейте привычку писать схему, это на многое влияет, а не просто стиль
  • (1 - 2 * Sign(CashOperation - 1)) - уродсво, т.к. не читабельно, у вас было понятнее, а это намного важнее (мой вариант не лучше вашего)
  • CashOperation = 3 - IMXO (по своему опыту всевозможных "операционных" систем) нарушает общий подход, экономили на спичках?
  • Почему поле Inc NULL-овское
  • CI.Cash_ID, CI.Period - ключ?
    ;WITH CashOperation (Cash_ID, DT, CashSum) AS (
    	SELECT	CASE CashOperation	-- 1,2 - полу-проводки, 3 двухсторонняя (из кассы в кассу)
    		WHEN 1 THEN     Cash_ID
    		WHEN 3 THEN Dst_Cash_ID
    		END	, DT, +CashSum FROM inserted WHERE CashOperation IN (1,3)
    UNION ALL
    	SELECT	Cash_ID	, DT, -CashSum FROM inserted WHERE CashOperation IN (2,3)
    )	UPDATE	CI
    	SET	Inc = IsNull(Inc,0) + CashSum	-- Зачем Inc NULL-овское поле
    	FROM  (	SELECT	 Cash_ID, DT, SUM(CashSum) AS CashSum
    		FROM	CashOperation
    		GROUP BY Cash_ID, DT
    		HAVING Sum(CashSum) != 0)	CS
    		JOIN dbo.TblRefCashInc		CI ON CI.Cash_ID = DC.Cash_ID
    						  AND CI.Period  > DC.DT	-- >= ?
    
  • 10 май 11, 17:52    [10631112]     Ответить | Цитировать Сообщить модератору
     Re: Остатки по месяцам  [new]
    orunbek
    Member

    Откуда: Гималай
    Сообщений: 2101
    Mnior
  • (1 - 2 * Sign(CashOperation - 1)) - уродсво, т.к. не читабельно, у вас было понятнее, а это намного важнее (мой вариант не лучше вашего)

  • согласен что не читабельно, но могу этим пожертвовать если это будет быстрее моего варианта

    Mnior
  • CashOperation = 3 - IMXO (по своему опыту всевозможных "операционных" систем) нарушает общий подход, экономили на спичках?

  • есть 3 кассовые операции: приход, расход и перемещение денег и все операции решил отразить в одной таблице

    Mnior
  • Почему поле Inc NULL-овское

  • Нет не NULL'овское, Inc=Inc+S заменил на IsNull(Inc,0)

    Mnior
  • CI.Cash_ID, CI.Period - ключ?

  • да
    10 май 11, 18:40    [10631303]     Ответить | Цитировать Сообщить модератору
     Re: Остатки по месяцам  [new]
    iljy
    Member

    Откуда:
    Сообщений: 8711
    orunbek
    Mnior
  • (1 - 2 * Sign(CashOperation - 1)) - уродсво, т.к. не читабельно, у вас было понятнее, а это намного важнее (мой вариант не лучше вашего)

  • согласен что не читабельно, но могу этим пожертвовать если это будет быстрее моего варианта

    Бытсрее будет не из-за sign (хотя оно тоже выиграшь даст ), а из-за меньшего количества обращений к таблице в запросе (2 вместо ваших 4х). Так что можете смело заменить на case если вам так понятнее.
    10 май 11, 18:46    [10631326]     Ответить | Цитировать Сообщить модератору
     Re: Остатки по месяцам  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    orunbek
    есть 3 кассовые операции: приход, расход и перемещение денег и все операции решил отразить в одной таблице
    Это я к тому, что можно было вместо типа перемещения + колонки Dst_Cash_ID писать две строки - дибит из одной кассы, кредит в другой. И не будет никаких UNION-ов и общего кода меньше и понятливее.
    Но всё зависит от внешних связей - архитектуры системы. (Видел когда полу-проводки были неудобным решением.) Касса это только малая часть системы.

    orunbek
    Mnior
  • Почему поле Inc NULL-овское

  • Нет не NULL'овское, Inc=Inc+S заменил на IsNull(Inc,0)
    Зачем?
    У вас же все колонки IS NOT NULL.
    Кста:
    UPDATE	CI
    SET	Inc += CashSum

    Вы поняли/применили мой вариант кода?

  • CI.Period > DC.DT - непонял, а почему остаток влияет только на последующие дни?
    Вы храните на начало дня, не на конец? XM, интересно. Как я вижу, из-за этого, код во многих местах будет пестрить аля DateAdd(Day,1,...
    Не?

    Так на всякий посоветую (поведал много говно-кода).
    Поставте сразу CHECK CONSTRAINT на
  • Amount > 0 (строго больше)
  • Amount % 0.01 = 0 (доли копеек)
    Money, собака, часто тупо не показывает доли, а ошибки можно ждать в самых неожиданных местах, поверьте. Всё тестами не покрыть, и всплывает в самый неподходящий момент. Не доверяйте никому: ни Линусу ни Биллику, ни Mnior-у ни себе.

    И ещё у вас не хватает обработки deleted при удалении или при изменении поля Canceled.
    Т.е. единый триггер на INSERT,UPDATE[,DELETE]

    Ладно, что-то я насоветовался, меру знать надо. Звиняйте за тон.
  • 10 май 11, 23:08    [10632193]     Ответить | Цитировать Сообщить модератору
     Re: Остатки по месяцам  [new]
    orunbek
    Member

    Откуда: Гималай
    Сообщений: 2101
    Mnior
    orunbek
    есть 3 кассовые операции: приход, расход и перемещение денег и все операции решил отразить в одной таблице
    Это я к тому, что можно было вместо типа перемещения + колонки Dst_Cash_ID писать две строки - дибит из одной кассы, кредит в другой. И не будет никаких UNION-ов и общего кода меньше и понятливее.
    Но всё зависит от внешних связей - архитектуры системы. (Видел когда полу-проводки были неудобным решением.) Касса это только малая часть системы.

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

    Mnior
    orunbek
    Нет не NULL'овское, Inc=Inc+S заменил на IsNull(Inc,0)
    Зачем?
    У вас же все колонки IS NOT NULL.
    Кста:
    UPDATE	CI
    SET	Inc += CashSum

    Я по ошибке написал Конечно наоборот IsNull(Inc,0): заменил на Inc=Inc+S

    Mnior
    Вы поняли/применили мой вариант кода?

    Еще нет, "исследую"...

    Mnior
  • CI.Period > DC.DT - непонял, а почему остаток влияет только на последующие дни?
    Вы храните на начало дня, не на конец? XM, интересно. Как я вижу, из-за этого, код во многих местах будет пестрить аля DateAdd(Day,1,...
    Не?

  • У меня остатки по месяцам и хранятся тока на начало месяца. А инструкция CI.Period > DC.DT суммирует все операции предыдущего месяца, из скрипта iljy взял

    Mnior
    Так на всякий посоветую (поведал много говно-кода).
    Поставте сразу CHECK CONSTRAINT на
  • Amount > 0 (строго больше)
  • Amount % 0.01 = 0 (доли копеек)
    Money, собака, часто тупо не показывает доли, а ошибки можно ждать в самых неожиданных местах, поверьте. Всё тестами не покрыть, и всплывает в самый неподходящий момент. Не доверяйте никому: ни Линусу ни Биллику, ни Mnior-у ни себе.

  • Встречался с проблемами округления, еще будучи бухгалтером 4 года назад ;-) С Excel'ом
    Но в моем случае хватит и двух цифр после запятой, но конечно на заметку взял

    Mnior
    И ещё у вас не хватает обработки deleted при удалении или при изменении поля Canceled.
    Т.е. единый триггер на INSERT,UPDATE[,DELETE]

    Само собой, есть и обработки на эти случаи, просто в данной теме INSERTED отражает общий принцип
    А на DELETE и UPDATE другие обработки, все не в одном коде потому что етсь и другие операции работающие в триггере
    А Canceled нету у меня, потому что это не настолько сложная система, максимум 2 пользователя активных и сумма кассовых операций в день не будет превышать 50ти записей

    Mnior
    Ладно, что-то я насоветовался, меру знать надо. Звиняйте за тон.


    Нет, спасибо большое за советы.
    В свое время знания в SQL и в частности под MS SQL был вообще минимален, я бы сказал вообще не было
    Но сейчас благодаря форуму sql.ru и пользователям которые всегда отзываются на проблемы и советуют, по чуть-чуть набираю опыт
    Спасибо большое
    11 май 11, 06:33    [10632697]     Ответить | Цитировать Сообщить модератору
     Re: Остатки по месяцам  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    orunbek
    А на DELETE и UPDATE другие обработки, все не в одном коде потому что етсь и другие операции работающие в триггере
    Можно несколько триггеров на события, вывести один общий для остатков. Главное чтоб логика в разных триггерах не пересекалась, а то порядок вызова триггеров нельзя тяжело контролировать.
    11 май 11, 19:23    [10637977]     Ответить | Цитировать Сообщить модератору
     Re: Остатки по месяцам  [new]
    orunbek
    Member

    Откуда: Гималай
    Сообщений: 2101
    Mnior
    orunbek
    А на DELETE и UPDATE другие обработки, все не в одном коде потому что етсь и другие операции работающие в триггере
    Можно несколько триггеров на события, вывести один общий для остатков. Главное чтоб логика в разных триггерах не пересекалась, а то порядок вызова триггеров нельзя тяжело контролировать.

    хорошо насчет последовательности сказали, надо про это тоже подумать
    11 май 11, 19:46    [10638048]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить