WITH CTE (mo, PlanPeriod, PlanID) AS
(
select 
--*
p.Mo, p.PlanPeriod, p.PlanID
from FIES.T_PLAN p 
join FIES.T_PLAN_REQUEST pr on p.PlanID = pr.PlanID
where --p.Mo = '501601' and 
exists (
select 
*
from FIES.T_PLAN_REQUEST_SMO_ITEM si
join FIES.T_PLAN_REQUEST_SMO s on s.PlanRequestSmoID = si.PlanRequestSmo
where s.[PlanRequest] = p.PlanID)
group by p.Mo, p.PlanPeriod, p.PlanID
)
select 

cast(
[dbo].[RegExReplace](
(
select *
from FIES.T_PLAN_REQUEST_SMO_ITEM si
join FIES.T_PLAN_REQUEST_SMO s on s.PlanRequestSmoID = si.PlanRequestSmo
join FIES.T_MONTH_VALUES mv on mv.MonthValuesID = si.MonthValuesID
where s.[PlanRequest] = p.PlanID
order by s.smo
for xml path('item')
)

, '<[^R].[^>]*>[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}<\/.[^>]*>', '') 
as varchar(max)) 
x
, p.PlanPeriod, p.Mo, ISNULL(p.CreateDate, CAST(CAST(pp.[Year] AS VARCHAR(4)) + '0101' AS datetime)) as CreateDate
,p.PlanID
into #tmp
--* --from cte
from FIES.T_PLAN p
join FIES.T_PLAN_REQUEST pr on p.PlanID = pr.PlanID
join FIES.T_PLAN_PERIOD pp on pp.PlanPeriodID = p.PlanPeriod
join CTE c on c.planid = p.planid
--order by p.CreateDate



    
select x
, (select top 1 t2.PlanID from #tmp t2 where t2.Mo = c1.Mo and t2.PlanPeriod = c1.PlanPeriod and t2.CreateDate = c1.CreateDate) as PlanID
,c1.CreateDate
, 'импортировано' as comment, c1.mo, c1.PlanPeriod
into #tmp2
from (
select mo, PlanPeriod, x, min(t1.CreateDate) as CreateDate
from #tmp t1
group by mo, PlanPeriod, x
) c1


INSERT INTO [FIES].[T_SMO_COEF_GROUP]
          ([SmoCoefGroupID]
          ,[DateStart]
          ,[Comment]
          ,[Mo]
          ,[PlanPeriod])
select PlanID, CreateDate, comment, mo, planperiod 
from 
(select *, row_number() over (partition by mo, PlanPeriod, PlanID order by mo, PlanPeriod, PlanID, LEN(x) desc ) num
from
#tmp2) t0
where t0.num = 1



--select * from #tmp  where mo = '500101' and planperiod = 'E550625C-6D74-450F-B8F1-3176AB1A314B'
drop table #tmp
drop table #tmp2


declare @type_ int
declare @periods nvarchar(max)
declare @sections nvarchar(max)
declare @sectionsNull nvarchar(max)
declare @sectionsNullSet nvarchar(max)
declare @sqlcommand nvarchar(max)

SELECT TOP 1 @type_ = EntityID FROM VCLIB.T_ENTITIES WHERE EntName = 'SMO_COEF_MONTH_VALUES'

SET @periods =
(SELECT PeriodAlias + ', '
FROM FIES.T_PERIOD_SECTION
ORDER BY [Priority]
for xml path('')) 
set @sections =
(SELECT SectionAlias + ', '
FROM FIES.T_PLAN_SECTION
ORDER BY SectionLevel
for xml path(''))

set @sectionsNull =
(SELECT N' AND ' + SectionAlias + N' IS NULL '
FROM FIES.T_PLAN_SECTION
ORDER BY SectionLevel
for xml path(''))

set @sectionsNullSet =
(SELECT N'NULL, '
FROM FIES.T_PLAN_SECTION
ORDER BY SectionLevel
for xml path(''))


--select @sections

set @sqlcommand = 
N'select newid() AS [MonthValuesID], ' + CAST(@type_ as nvarchar(10)) +' as type_,'+
@periods + @sections
+N'  
s.Smo,cg.SmoCoefGroupID 
into #temp_items
from  FIES.T_PLAN_REQUEST_SMO_ITEM si
join [FIES].[T_MONTH_VALUES] mv on mv.[MonthValuesID] = si.[MonthValuesID]
join FIES.T_PLAN_REQUEST_SMO s on s.PlanRequestSmoID = si.PlanRequestSmo
join [FIES].[T_SMO_COEF_GROUP] cg on s.[PlanRequest] = cg.[SmoCoefGroupID]

INSERT INTO [FIES].[T_MONTH_VALUES]
           ([MonthValuesID],
		   '+@periods+'
            [type_])
select [MonthValuesID], '+@periods+' [type_]
from #temp_items

INSERT INTO [FIES].[T_SMO_COEF_MONTH_VALUES]
           ([MonthValuesID],
		   '+@sections+'
            [SmoCoefGroup]
           ,[Smo]
           )
SELECT [MonthValuesID], '+@sections+'
[SmoCoefGroupID],[Smo]
from #temp_items

drop table #temp_items
'
 
--select @sqlcommand

exec sp_sqlexec @sqlcommand

set @sqlcommand = 
'select newid() AS [MonthValuesID], ' + CAST(@type_ as nvarchar(10)) +' as type_,'+
@periods
+N'  
vd.Smo,cg.SmoCoefGroupID 
into #temp_items
from [FIES].[T_SMO_COEF_GROUP] cg
join [FIES].[T_SMO_COEF_MONTH_VALUES_DEFAULT] vd on vd.Mo = cg.Mo
join [FIES].[T_MONTH_VALUES] mv on mv.[MonthValuesID] = vd.[MonthValuesID]
where not exists (select * from [FIES].[T_SMO_COEF_MONTH_VALUES] scmv
				  where scmv.SmoCoefGroup = cg.SmoCoefGroupID '+ @sectionsNull +' )


INSERT INTO [FIES].[T_MONTH_VALUES]
           ([MonthValuesID],
		   '+@periods+'
            [type_])
select [MonthValuesID], '+@periods+' [type_]
from #temp_items

INSERT INTO [FIES].[T_SMO_COEF_MONTH_VALUES]
           ([MonthValuesID],
		   '+@sections+'
            [SmoCoefGroup]
           ,[Smo]
           )
SELECT [MonthValuesID], '+@sectionsNullSet+'
[SmoCoefGroupID],[Smo]
from #temp_items

drop table #temp_items
'

--select @sqlcommand
exec sp_sqlexec @sqlcommand






/*
select 
--*
p.Mo, p.PlanPeriod, p.PlanID
from FIES.T_PLAN_REQUEST_SMO_ITEM si
join FIES.T_MONTH_VALUES mv on mv.MonthValuesID = si.MonthValuesID
join FIES.T_PLAN_REQUEST_SMO s on s.PlanRequestSmoID = si.PlanRequestSmo
join FIES.T_PLAN p on p.PlanID = s.PlanRequest
join FIES.T_PLAN_REQUEST pr on p.PlanID = pr.PlanID
where p.PlanID in ('947DAA07-F1CB-4223-8D81-A73892552F1B',
'D1323727-D173-4925-B7B5-AE6094D3B2AB')
order by p.PlanID
*/