CREATE TABLE [dbo].[Costs] (
	[WareCode] [int] NOT NULL ,
	[Amount] [int] NOT NULL ,
	[Price] [money] NOT NULL ,
	[DateIn] [datetime] NOT NULL ,
	[ID] [int] IDENTITY ( 1 ,  1 ) NOT NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Costs] ADD 
	CONSTRAINT [PK_Costs] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Costs] UNIQUE  NONCLUSTERED 
	(
		[WareCode],
		[Amount],
		[Price],
		[DateIn]
	)  ON [PRIMARY] 
GO

CREATE TABLE [dbo].[Leavings] (
	[WareCode] [int] NOT NULL ,
	[Contr] [int] NULL ,
	[Col1] [int] NOT NULL ,
	[Col3] [int] NULL ,
	[DateIn] [datetime] NULL ,
	[PriceRel] [money] NULL ,
	[ID] [int] IDENTITY ( 1 ,  1 ) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Leavings] ADD 
	CONSTRAINT [PK_Leavings] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_Leavings] UNIQUE  NONCLUSTERED 
	(
		[WareCode],
		[DateIn]
	)  ON [PRIMARY] 
GO

 -- запасы нарастающим итогом по DateIn 
CREATE VIEW dbo.Leavings_Cumulative
AS
SELECT     ID, WareCode, Contr, Col1, Col3, DateIn, PriceRel, ISNULL
                          ((SELECT     SUM(Col1)
                              FROM         Leavings X
                              WHERE     X.WareCode = T.WareCode AND X.DateIn < T.DateIn AND X.Col1 >  0 ),  0 ) AS Col1_prev
FROM         dbo.Leavings T
WHERE     (Col1 >  0 )
GO

 -- сгруппированные по WareCode расходы 
CREATE VIEW Costs_Grouped
AS
SELECT WareCode, SUM(Amount) Amount   
FROM Costs C 
GROUP BY WareCode
GO

 -- удовлетворенные расходы с разбивкой по строкам таблицы запасов 
CREATE VIEW dbo.Costs_Contented
AS
SELECT     C.WareCode, L.ID, L.DateIn, CASE WHEN L.Col1_prev + L.Col1 < C.Amount THEN L.Col1 ELSE (C.Amount - L.Col1_prev) 
                      END AS Amount_Contented
FROM         dbo.Costs_Grouped C INNER JOIN
                      dbo.Leavings_Cumulative L ON C.WareCode = L.WareCode AND L.Col1_prev < C.Amount
GO

 --   расходы в порядке DateIn нарастающим итогом 
CREATE VIEW dbo.Costs_Cumulative
AS
SELECT     WareCode, Amount, Price, DateIn, ISNULL
                          ((SELECT     SUM(Amount)
                              FROM         Costs X
                              WHERE     X.WareCode = T.WareCode AND X.DateIn < T.DateIn),  0 ) AS Amount_prev
FROM         dbo.Costs T
GO

 --   удовлетворенные расходы нарастающим  в порядке DateIn нарастающим итогом 
CREATE VIEW dbo.Costs_Contented_Cumulative
AS
SELECT     ID, WareCode, DateIn, Amount_Contented, ISNULL
                          ((SELECT     SUM(Amount_Contented)
                              FROM         Costs_Contented X
                              WHERE     X.WareCode = T.WareCode AND X.DateIn < T.DateIn),  0 ) AS Amount_Contented_prev
FROM         dbo.Costs_Contented T
GO

 --   удовлетворенные расходы связаны со строками таблицы запасов, за счет которых проведены 
CREATE VIEW dbo.Costs_Contented_Detailed
AS
SELECT     CC.WareCode, CC.Amount, CC.Price, CC.DateIn AS cDateIn, CC.Amount_prev, CCC.ID, CCC.DateIn AS lDateIn, CCC.Amount_Contented, 
                      CCC.Amount_Contented_prev
FROM         dbo.Costs_Cumulative CC LEFT OUTER JOIN
                      dbo.Costs_Contented_Cumulative CCC ON CC.WareCode = CCC.WareCode AND (CCC.Amount_Contented_prev >= CC.Amount_prev AND 
                      CCC.Amount_Contented_prev < CC.Amount_prev + CC.Amount OR
                      CCC.Amount_Contented_prev + CCC.Amount_Contented > CC.Amount_prev AND 
                      CCC.Amount_Contented_prev + CCC.Amount_Contented < CC.Amount_prev + CC.Amount)
GO

 --   удовлетворенные расходы Amount_Contented_Here + связаны со строками таблицы запасов, за счет которых проведены 
CREATE VIEW dbo.Costs_Leaving_Contented_Detailed
AS
SELECT     WareCode, Amount, Price, cDateIn, ID, lDateIn, (CASE WHEN (Amount_Contented_prev >= Amount_prev) AND 
                      (Amount_Contented_prev + Amount_Contented <= Amount_prev + Amount) THEN Amount_Contented WHEN (Amount_Contented_prev < Amount_prev) 
                      AND (Amount_Contented_prev + Amount_Contented <= Amount_prev + Amount) THEN Amount_Contented - (Amount_prev - Amount_Contented_prev) 
                      WHEN (Amount_Contented_prev < Amount_prev) AND (Amount_Contented_prev + Amount_Contented > Amount_prev + Amount) 
                      THEN Amount ELSE (- Amount_Contented_prev + Amount_prev - Amount) END) Amount_Contented_Here
FROM         dbo.Costs_Contented_Detailed
WHERE     ID IS NOT NULL
GO

 --   группированные удовлетворенные расходы 
CREATE VIEW dbo.Costs_Contented_Grouped
AS
SELECT     WareCode, Amount, Price, cDateIn, SUM(Amount_Contented_Here) AS Amount_Contented_Here
FROM         dbo.Costs_Leaving_Contented_Detailed C
GROUP BY WareCode, Amount, Price, cDateIn
GO

 --   Обновление для таблицы Leavings.  UPDATE написать могет каждый 
CREATE VIEW dbo.Leavings_Update
AS
SELECT     C.ID, L.WareCode, L.Contr, L.Col1, SUM(C.Amount_Contented_Here) AS Col3, L.DateIn, SUM(C.Amount_Contented_Here * C.Price) AS PriceRel
FROM         dbo.Costs_Leaving_Contented_Detailed C INNER JOIN
                      dbo.Leavings L ON C.ID = L.ID
GROUP BY C.ID, L.WareCode, L.Contr, L.Col1, L.DateIn

GO
 --   Добавление для таблицы Leavings. ADD написать могет каждый 
CREATE VIEW dbo.Leavings_Add
AS
SELECT     C.WareCode, NULL AS Contr,  0  AS Col1, C.Amount - ISNULL(CC.Amount_Contented_Here,  0 ) AS Col2, NULL AS DateIn, 
                      (C.Amount - ISNULL(CC.Amount_Contented_Here,  0 )) * CC.Price AS PriceRel
FROM         dbo.Costs_Contented_Grouped CC RIGHT OUTER JOIN
                      dbo.Costs C ON CC.WareCode = C.WareCode AND CC.Amount = C.Amount AND CC.Price = C.Price AND CC.cDateIn = C.DateIn
WHERE     (CC.Amount_Contented_Here < C.Amount) OR
                      (CC.Amount_Contented_Here IS NULL)
GO