if exists ( select * from sys.procedures p where p.name = 'spx_LoadItemPlanModification' ) DROP PROCEDURE [dbo].[spx_LoadItemPlanModification]
GO
-- =============================================
-- Description: Тестовая хранимка по "загрузке плана", генерирует план из факта
-- =============================================
CREATE PROCEDURE [dbo].[spx_LoadItemPlanModification]
    @TableName varchar(max)
AS 
BEGIN 

    declare @sql nvarchar(max)

    delete from [IES].[T_ITEM_PLAN_MODIFICATION]

    select @sql = N'INSERT INTO [IES].[T_ITEM_PLAN_MODIFICATION] ([ItemPlanModificationID], [ModificationDate], [PlanMonth], [PlanQuarter], [PeriodYear], [' + sCols.names + N'], [' + pCols.names + N'], [Value])'
            + N'SELECT NEWID(), GETDATE(), '
            + N'PlanMonth,'
            + N'PlanQuarter,'
            + N'PeriodYear, '
            + N'[' + sCols.names + N'],'
            + N'[' + pCols.names + N'],'
            + N'[Value]'
            + N'from [IES].[T_SCHET_ITEM_FAKT]'
    from (
            select cast(STRING_AGG(c.name, N'] ,[') as nvarchar(max)) as names
            from sys.columns c
                join sys.tables t on t.object_id = c.object_id
                join sys.schemas s on s.schema_id = t.schema_id
            where s.name = 'IES' and t.name = 'T_PLAN_CLASSIFICATION' and c.name not in ('PlanClassificationID')
        ) sCols,
        (
            select cast(STRING_AGG(ps.SectionAlias, '] ,[') as nvarchar(max)) as names
            from IES.T_PLAN_SECTION ps
            where ps.ClassificationType = 1
        ) pCols

    exec sp_executesql @sql

END
