CREATE TABLE dbo.BusinessEvent(
	EventDate datetime NOT NULL DEFAULT (GETDATE()),
	AgentID int NOT NULL,
	EventID int NOT NULL,
 CONSTRAINT PK_BusinessEvent PRIMARY KEY CLUSTERED(EventDate ASC))

CREATE TABLE dbo.Warehouse(
	RefKey int IDENTITY( 1 , 1 ) NOT NULL,
	EventDate datetime NOT NULL,
	ProductID int NOT NULL,
	Amount int NOT NULL,
 CONSTRAINT [PK_Warehouse] PRIMARY KEY(RefKey ASC))

CREATE TABLE dbo.GoodsMovement(
	RefKey int NOT NULL,
	EventDateOut datetime NOT NULL,
	AmountOut int NOT NULL,
	ProductID int NOT NULL,
	EventDateIN datetime NOT NULL,
	AmountIn int NOT NULL,
 CONSTRAINT [PK_GoodsMovement] PRIMARY KEY(RefKey ASC))

-- от контрагента №3 20.10.2006 14:10:02 поступили ТМЦ двух видов
-- 1. № 1 10 уч.ед. на сумму 100
-- 2. № 2 15 уч.ед. на сумму 180
DECLARE @EventDate datetime
DECLARE @Counter int
DECLARE @Qty int
DECLARE @Rest int
DECLARE @Amount int

SET @EventDate = CONVERT(datetime, '20061020 14:10:02',  113 )
INSERT INTO BusinessEvent VALUES(
	@EventDate
	,  3 
	,  1 
	)
-- товар № 1 
SET @Rest =  100 
SET @Qty =  10 
SET @Amount = CONVERT(int, @Rest / @Qty)
SET @Counter =  1 
WHILE @Counter < @Qty
	BEGIN
		INSERT INTO Warehouse VALUES(
			@EventDate
			,  1 
			, @Amount
		)
		SET @Rest = @Rest - @Amount
		SET @Counter = @Counter +  1 
	END
INSERT INTO Warehouse VALUES(
	@EventDate
	,  1 
	, @Rest
)

-- товар № 2
SET @Rest =  180 
SET @Qty =  15 
SET @Amount = CONVERT(int, @Rest / @Qty)
SET @Counter =  1 
WHILE @Counter < @Qty
	BEGIN
		INSERT INTO Warehouse VALUES(
			@EventDate
			,  2 
			, @Amount
		)
		SET @Rest = @Rest - @Amount
		SET @Counter = @Counter +  1 
	END
INSERT INTO Warehouse VALUES(
	@EventDate
	,  2 
	, @Rest
)
-- следующая поставка
-- от контрагента №1 20.10.2006 15:18:33 поступили ТМЦ 
-- 1. № 1 8 уч.ед. на сумму 75
SET @EventDate = CONVERT(datetime, '20061020 15:18:33',  113 )
INSERT INTO BusinessEvent VALUES(
	@EventDate
	,  1 
	,  1 
	)
-- товар № 1 
SET @Rest =  75 
SET @Qty =  8 
SET @Amount = CONVERT(int, @Rest / @Qty)
SET @Counter =  1 
WHILE @Counter < @Qty
	BEGIN
		INSERT INTO Warehouse VALUES(
			@EventDate
			,  1 
			, @Amount
		)
		SET @Rest = @Rest - @Amount
		SET @Counter = @Counter +  1 
	END
INSERT INTO Warehouse VALUES(
	@EventDate
	,  1 
	, @Rest
)

-- отгрузим контрагенту № 5 21.10.2006 10:00:00
-- 2 уч.ед. ТМЦ № 1 на сумму 30
-- допустим учет ведется по LIFO

DECLARE @ProductID int
DECLARE @RefKey int

SET @EventDate = CONVERT(datetime, '20061021 10:00:00',  113 )
INSERT INTO BusinessEvent VALUES(
	@EventDate
	,  5 
	,  2 
	)
-- товар № 1 
SET @ProductID =  1 
SET @Rest =  30 
SET @Qty =  2 
SET @Amount = CONVERT(int, @Rest / @Qty)
SET @Counter =  1 

WHILE @Counter < @Qty
	BEGIN
		SET @RefKey = (
				SELECT   W.RefKey
				FROM (SELECT TOP  1  * FROM Warehouse ORDER BY EventDate DESC) AS W
				WHERE W.ProductID = @ProductID
				)
		INSERT INTO GoodsMovement ( RefKey
      , EventDateOut
      , AmountOut
      , ProductID
      , EventDateIN
      , AmountIn
		)
		SELECT  @RefKey
				, @EventDate
				, @Amount
				, W.ProductID
				, W.EventDate
				, W.Amount
		FROM Warehouse W
		WHERE W.RefKey = @RefKey

		DELETE FROM Warehouse WHERE RefKey = @RefKey

			SET @Rest = @Rest - @Amount
			SET @Counter = @Counter +  1 
	END
	SET @RefKey = (
			SELECT   W.RefKey
			FROM (SELECT TOP  1  * FROM Warehouse ORDER BY EventDate DESC) AS W
			WHERE W.ProductID = @ProductID
			)

	INSERT INTO GoodsMovement ( RefKey
   , EventDateOut
   , AmountOut
   , ProductID
   , EventDateIN
   , AmountIn
	)
	SELECT  @RefKey
			, @EventDate
			, @Amount
			, W.ProductID
			, W.EventDate
			, W.Amount
	FROM Warehouse W
	WHERE W.RefKey = @RefKey
	
DELETE FROM Warehouse WHERE RefKey = @RefKey

	INSERT INTO GoodsMovement ( RefKey
   , EventDateOut
   , AmountOut
   , ProductID
   , EventDateIN
   , AmountIn
	)
	SELECT  @RefKey
			, @EventDate
			, @Rest
			, W.ProductID
			, W.EventDate
			, W.Amount
	FROM Warehouse W
	WHERE W.RefKey = @RefKey
	
DELETE FROM Warehouse WHERE RefKey = @RefKey

SELECT ALL EventDate
	, ProductID
	, COUNT(*) AS Qty
	, SUM(Amount) AS Total
FROM Warehouse
GROUP BY EventDate, ProductID