Я думаю, что у меня проблемы с цитированием. я хочу заменить
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_2Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_3Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_4Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_5Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_6Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_7Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_8Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 8 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_9Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_10Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_11Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_12Mp'
,CAST(REPLACE([NET_VALUE_1M],',','.') as float)/SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float)) OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_13Mp'
с циклом while
SELECT
CAST(REPLACE([NET_VALUE_1M],',','.') as float)
/
SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float))
OVER (PARTITION BY [ID] ORDER BY [CMONTH]
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as 'NET_VALUE_1M_PROP_2Mp'
DECLARE @cnt INT =2;
DECLARE @cnt_total INT =12;
WHILE @cnt < @cnt_total
BEGIN
DECLARE @SQL NVARCHAR(MAX);
SET @SQL =',CAST(REPLACE([NET_VALUE_1M],',','.') as float)
/
SUM(CAST(REPLACE([NET_VALUE_1M],',','.') as float))
OVER (PARTITION BY [ID] ORDER BY [CMONTH] ROWS
BETWEEN @cnt PRECEDING AND CURRENT ROW)
as ['NET_VALUE_1M_PROP_']+@cnt+['Mp']'
EXECUTE(@SQL)
SET @cnt=@cnt+1;
END;
FROM [Channel_AGG]
вызывает ошибку:
Сообщение 102, уровень 15, состояние 1, строка 13
Неверный синтаксис рядом с ','.Сообщение 156, уровень 15, состояние 1, строка 20
Неверный синтаксис рядом с ключевым словом "ОТ".
Как работает цитирование для объявления команды SQL? Я пробовал ['
открывающую цитату и ']
закрывающую цитату, как показано здесь -loop">Как создать/добавить столбцы с помощью переменной в цикле, но ошибки продолжаются. Итак, чтобы создать начальную команду внутри цикла for в SQL Server 2014?
DELARE @SQL NVARCHAR(MAX)
, но проблема с цитированием сохраняется. - person hhh   schedule 21.04.2017