USE tempdb;
GO
CREATE TABLE [dbo].[tblNodeElement] (
	[iNodeElementID]	Int
	CONSTRAINT [PK_tblNodeElement] PRIMARY KEY
,	[iElementID]		Int		NULL
,	[iElementID_Parent]	Int		NULL
--	CONSTRAINT [FK_tblNodeElement_Parent] REFERENCES [dbo].[tblNodeElement] ([iElementID])	-- Офигенски спроектированная табли
--,	[iEdizmID]		Int		NULL	-- Не по теме
,	[decNodeElementCnt]	Money		NULL
)
CREATE INDEX [IX_iElementID] ON [dbo].[tblNodeElement] ([iElementID])INCLUDE([iElementID_Parent])
CREATE INDEX [IX_iElementID_Parent] ON [dbo].[tblNodeElement] ([iElementID_Parent])
CREATE TABLE [dbo].[tblZehNorm] (
	[iZehNormID]		Int
	CONSTRAINT [PK_tblZehNorm] PRIMARY KEY
,	[iContractorID]		Int	NULL
,	[iNodeElementID]	Int	NULL
	CONSTRAINT [FK_tblZehNorm_tblNodeElement] REFERENCES [dbo].[tblNodeElement] ([iNodeElementID])
,	[decZehNorm]		Money	NULL

) ON [PRIMARY]
GO
--------------------------------------------------------------------------------
INSERT dbo.tblNodeElement (iNodeElementID,iElementID,iElementID_Parent) VALUES
 (21875,6020,6000)
,(41853,   1,6005)
,(42482,   1,6011)
,(42718,   1,6015)
,(42850,   1,6016)
,(43059,   1,6020)
,(43626,   1,6023)
,(47117,   1,6000)
,(54976,6011,6000)
,(56466,6005,6000)
,(58645,6000,NULL)
,(75556,6023,6000)
,(79121,6016,6000)
,(86558,6015,6000)
INSERT dbo.tblZehNorm (iZehNormID,iContractorID,iNodeElementID,decZehNorm) VALUES
 (25148, 4,41853,  6.000)
,(25816, 4,42482,  4.000)
,(26052, 4,42718,  5.000)
,(26185, 4,42850,  5.000)
,(27006, 4,43626, 40.000)
,(31234, 6,47117, 10.000)
,(31238, 1,47117, 67.730)
,(31239, 4,47117, 37.000)
,(31240, 2,47117,114.000)
,(38160, 9,47117, 53.000)
,(38561, 8,43059,110.000)
,(39337, 8,47117,109.000)
,(40861,10,47117, 58.500)
,(41923,11,47117, 27.400)
GO
--------------------------------------------------------------------------------
CREATE TABLE [dbo].[User] (
	[ID]		Int	NOT NULL	PRIMARY KEY
)
-- Модель
CREATE TABLE [dbo].[Product] (
	[ID]		Int	NOT NULL	PRIMARY KEY
)
CREATE TABLE [dbo].[Node] (	-- Компонока узлов продукта
	[ID]		Int	NOT NULL	PRIMARY KEY
,	[Product]	Int	NOT NULL	REFERENCES [dbo].[Product]	([ID])
,	[Parent]	Int         NULL	REFERENCES [dbo].[Node]		([ID])
,	[Count]		Int	NOT NULL	DEFAULT (1)
)
CREATE INDEX [IX_Product] ON [dbo].[Node] ([Product])
CREATE INDEX [IX_Parent]  ON [dbo].[Node] ([Parent])
CREATE TABLE [dbo].[Detail] (	-- Материалы/детали
	[ID]		Int	PRIMARY KEY
)
CREATE TABLE [dbo].[Assembly] (	-- Возможная компоновка узлов деталями
	[Node]		Int	NOT NULL	REFERENCES [dbo].[Node]		([ID])
,	[Detail]	Int	NOT NULL	REFERENCES [dbo].[Detail]	([ID])
,	PRIMARY KEY ([Node],[Detail])
)
-- Нормы расходов деталей по потребителям для каждой компановки
CREATE TABLE [dbo].[DetailConsumptionRate] (
	[User]		Int	NOT NULL	REFERENCES [dbo].[User]		([ID])
,	[Detail]	Int	NOT NULL
,	[Node]		Int	NOT NULL
,	[Rate]		Money	NOT NULL
,	PRIMARY KEY ([User],[Detail],[Node])
,	FOREIGN KEY ([Node],[Detail])		REFERENCES [dbo].[Assembly]	([Node],[Detail])
)
-- Реальные изделия
CREATE TABLE [dbo].[Article] (
	[ID]		Int	NOT NULL	PRIMARY KEY
,	[Product]	Int	NOT NULL	REFERENCES [dbo].[Product]	([ID])
,	[User]		Int	NOT NULL	REFERENCES [dbo].[User]		([ID])
)
CREATE TABLE [dbo].[Consist] (	-- наполнение
	[Article]	Int	NOT NULL	REFERENCES [dbo].[Article]	([ID])
,	[Node]		Int	NOT NULL	REFERENCES [dbo].[Node]		([ID])
,	[Detail]	Int	NOT NULL	REFERENCES [dbo].[Detail]	([ID])
,	[Count]		Int	NOT NULL	DEFAULT(1)
,	PRIMARY KEY ([Article],[Detail],[Node])
)
GO
--------------------------------------------------------------------------------
-- Преобразовние старой модели данных в новую
INSERT	dbo.[User](ID)
SELECT	DISTINCT iContractorID
FROM	dbo.tblZehNorm

INSERT	dbo.Product(ID)
SELECT	DISTINCT iElementID
FROM	dbo.tblNodeElement
WHERE	iElementID_Parent IS NULL

INSERT	dbo.Detail(ID)
SELECT	DISTINCT E.iElementID
FROM	dbo.tblZehNorm	N
LEFT JOIN dbo.tblNodeElement	E ON E.iNodeElementID = N.iNodeElementID

;WITH [Path] AS (
	SELECT	P.iNodeElementID	AS [Root]
	,	P.iElementID
	FROM	dbo.tblNodeElement	P
	WHERE	iElementID_Parent IS NULL
UNION ALL
	SELECT	P.[Root]
	,	N.iElementID
	FROM	[Path]			P
	JOIN	dbo.tblNodeElement	N ON N.iElementID_Parent = P.iElementID
)
INSERT	dbo.Node(ID,Product,Parent,[Count])
SELECT	DISTINCT N.iNodeElementID, X.iElementID, NullIf(P.iNodeElementID,R.[Root]), IsNull(N.decNodeElementCnt,1)
FROM	dbo.tblNodeElement	N
LEFT JOIN (SELECT DISTINCT*FROM [Path]) R ON R.iElementID = N.iElementID
LEFT JOIN dbo.tblNodeElement	P ON P.iElementID = N.iElementID_Parent
LEFT JOIN dbo.tblNodeElement	X ON X.iNodeElementID = R.[Root]

INSERT	dbo.[Assembly](Node,Detail)
SELECT	DISTINCT E.iNodeElementID,E.iElementID
FROM	dbo.tblZehNorm	N
LEFT JOIN dbo.tblNodeElement	E ON E.iNodeElementID = N.iNodeElementID

INSERT	dbo.DetailConsumptionRate([User],[Detail],[Node],Rate)
SELECT	N.iContractorID, E.iElementID, E.iNodeElementID, N.decZehNorm
FROM	dbo.tblZehNorm	N
LEFT JOIN dbo.tblNodeElement	E ON E.iNodeElementID = N.iNodeElementID

INSERT	dbo.Article(ID,Product,[User])
SELECT	ID,ID,4	-- ХЗ
FROM	dbo.Product

INSERT	dbo.Consist(Article,[Node],Detail,[Count])
SELECT	A.ID, E.iNodeElementID, E.iElementID, IsNull(E.decNodeElementCnt,1)
FROM	dbo.Article	A
JOIN	dbo.tblZehNorm	N ON N.iContractorID = A.[User]
JOIN	dbo.tblNodeElement E ON E.iNodeElementID = N.iNodeElementID
GO
--------------------------------------------------------------------------------
-- Показ имеющихся данных
SELECT	*
FROM		dbo.tblNodeElement	NE
LEFT JOIN	dbo.tblZehNorm		ZN ON NE.iNodeElementID	= ZN.iNodeElementID

SELECT	C.*,R.Rate,N.Parent
FROM	dbo.Consist	C
JOIN	dbo.Article	A ON A.ID = C.Article
JOIN	dbo.DetailConsumptionRate	R ON R.[User]	= A.[User]
					 AND R.Node	= C.Node
					 AND R.Detail	= C.Detail
JOIN	dbo.Node	N ON N.ID = C.Node

DECLARE	@iElementID_Root	Int	= 6000
,	@iElementID		Int	= 1
,	@iContractorID		Int	= 4;
--------------------------------------------------------------------------------
-- Первоначальный запрос
WITH [NodeLevel] AS (
	SELECT	iNodeElementID
	,	iElementID			AS iElementID_Root
	,	iElementID_Parent
	,	iElementID
	,	decNodeElementCnt
	,	IsNull(decNodeElementCnt,0)	AS decNodeElementCntSum
	FROM	dbo.tblNodeElement AS nd
	WHERE (iElementID_Parent IS NULL)
UNION ALL
	SELECT	NE.iNodeElementID
	,	NL.iElementID_Root
	,	NE.iElementID_Parent
	,	NE.iElementID
	,	NE.decNodeElementCnt
	,	IsNull(NL.decNodeElementCnt,0) * IsNull(ne.decNodeElementCnt,0)	AS decNodeElementCntSum
	FROM	[NodeLevel]		NL
	JOIN	dbo.tblNodeElement	NE ON NE.iElementID_Parent = NL.iElementID
)	SELECT	Sum(CASE WHEN ZN.iContractorID = @iContractorID THEN X.Norm ELSE 0 END)	AS SumZeh
	,	Sum(X.Norm)								AS SumIzd
/*	SELECT	CASE WHEN ZN.iContractorID = @iContractorID THEN X.Norm ELSE 0 END	AS Zeh
	,	X.Norm									AS Izd
*/	FROM	[NodeLevel]	NL
	JOIN	dbo.tblZehNorm	ZN ON NL.iNodeElementID = ZN.iNodeElementID
	CROSS APPLY (SELECT decZehNorm * IsNull(NullIf(NL.decNodeElementCntSum,0),1) / IsNull(NullIf(NL.decNodeElementCnt,0),1))X (Norm)
	WHERE	iElementID_Root	= @iElementID_Root
	AND	iElementID	= @iElementID

-- Обратный вариант (по теме поиска снизу вверх)
;WITH [Parents] AS (
	SELECT	P.iElementID
	,	P.iElementID_Parent
	FROM	dbo.tblNodeElement	P
	WHERE	P.iElementID = @iElementID	-- Тупо оптимизация
UNION ALL
	SELECT	E.iElementID
	,	P.iElementID_Parent
	FROM	[Parents]		E
	JOIN	dbo.tblNodeElement	P ON P.iElementID = E.iElementID_Parent
)	SELECT	Sum(CASE WHEN ZN.iContractorID = @iContractorID THEN X.Norm ELSE 0 END)	AS ZehNormSum
	,	Sum(X.Norm)								AS IzdNormSum
	FROM	dbo.tblNodeElement	NE
	JOIN	dbo.tblZehNorm		ZN ON NE.iNodeElementID	= ZN.iNodeElementID
CROSS	APPLY (SELECT ZN.decZehNorm * 1	--	IsNull(NL.decNodeElementCnt * NL.decNodeElementCnt_Parent,1)	-- ?
				)	X (Norm)
	WHERE	NE.iElementID = @iElementID

-- На чистой модели
SELECT	C.Article
,	C.Detail
,	Sum(C.[Count] * R.Rate)
FROM	dbo.Consist	C
JOIN	dbo.Article	A ON A.ID = C.Article
JOIN	dbo.DetailConsumptionRate	R ON R.[User]	= A.[User]
					 AND R.Node	= C.Node
					 AND R.Detail	= C.Detail
GROUP BY C.Article
	,C.Detail
GO
-- DROP TABLE [dbo].[tblZehNorm],[dbo].[tblNodeElement];
-- DROP TABLE [dbo].[Consist],[dbo].[Article],[dbo].[DetailConsumptionRate],[dbo].[Assembly],[dbo].[Detail],[dbo].[Node],[dbo].[Product],[dbo].[User];