--План с документа - Документ.ТоварнаяНоменклатураСборки
DECLARE @period date;
SET @period='01.01.4019';

WITH tm_price(id_nomen, nomenclature, id_type, type_price, cost)
	as(
		SELECT T1._nomen as id_nomen, nomen._Description as nomenclature, T1._type as id_type, typePrice._Description as type_price, T1.cost as cost
		FROM (
			SELECT	T4._period as _period,
					T4._Fld3677RRef as _nomen,
					T4._Fld3678RRef as _type,
					T4._Fld3680 as cost
			FROM (
				SELECT  T3._Fld3677RRef as _nomen, T3._Fld3678RRef as _type,
						max(T3._period) as max_period
				FROM _InfoRg3676 as T3
				GROUP BY T3._Fld3677RRef, T3._Fld3678RRef
				) as T2
			INNER JOIN _InfoRg3676 as T4 on T2._nomen = T4._Fld3677RRef and T2._type = T4._Fld3678RRef and T2.max_period = T4._Period) T1
			LEFT JOIN _Reference25 as nomen on T1._nomen = nomen._IDRRef --Справочник.Номенклатура
			LEFT JOIN _Reference56 as typePrice on T1._type = typePrice._IDRRef
		WHERE typePrice._Description in ('Действующая', 'Стабильная')
	)


SELECT doc_plan._Fld6397 as date_plan, doc_plan._Fld6398 as date_plan_month,
	sum(tp_doc_plan._Fld6407) as amount_plan, sum(tp_doc_plan._Fld6407*priceCurrent.cost) as sum_current_plan, sum(tp_doc_plan._Fld6407*priceStable.cost) as sum_stable_plan
FROM _Document6395 as doc_plan --Документ.ТоварнаяНоменклатураСборки
	LEFT JOIN _Document6395_VT6404 as tp_doc_plan on doc_plan._IDRRef = tp_doc_plan._Document6395_IDRRef --Документ.ТоварнаяНоменклатураСборки.НоменклатураТЧ
	LEFT JOIN _Reference25 as nomen on tp_doc_plan._Fld6406RRef = nomen._IDRRef --Справочник.Номенклатура
	LEFT JOIN _Reference25 as nomen_parent on nomen_parent._IDRRef = nomen._ParentIDRRef --Справочник.Номенклатура.Родитель
	LEFT JOIN tm_price as priceCurrent on priceCurrent.id_nomen = tp_doc_plan._Fld6406RRef and priceCurrent.type_price = 'Действующая'
	LEFT JOIN tm_price as priceStable on priceStable.id_nomen = tp_doc_plan._Fld6406RRef and priceStable.type_price = 'Стабильная'
WHERE CONVERT(varchar(max), doc_plan._marked, 2) = '00' and CONVERT(varchar(max), doc_plan._posted, 2) = '01'
	and CAST(doc_plan._date_time as date) >= @period
	and nomen_parent._Code NOT IN (27764, 29491)
GROUP BY doc_plan._Fld6397, doc_plan._Fld6398

WITH tm_price
	as (
		SELECT TOP 1000  ROW_NUMBER() OVER(PARTITION BY nomen._Description, typePrice._Description ORDER BY price._Period ASC) AS RowNumber,
			price._Period as date_price, nomen._Description as nomen, typePrice._Description as type_price, price._Fld3680 as cost
		FROM _InfoRg3676 as price
			LEFT JOIN _Reference25 as nomen on price._Fld3677RRef = nomen._IDRRef --Справочник.Номенклатура
			LEFT JOIN _Reference56 as typePrice on price._Fld3678RRef = typePrice._IDRRef
		WHERE typePrice._Description in ('Действующая', 'Стабильная')	
		)

SELECT tm1.RowNumber, tm1.nomen, tm1.type_price, tm1.cost, 
	tm1.date_price, ISNULL(tm2.date_price, DATEADD(year, +2000, GETDATE())) as date_price2
FROM tm_price as tm1
	LEFT JOIN tm_price as tm2 on tm1.nomen = tm2.nomen and tm1.type_price = tm2.type_price and tm1.RowNumber = tm2.RowNumber-1