Kod: Zaznacz cały
SELECT cdn.numerdokumentu(cdn.dokmaptypdokumentu(zan_gidtyp,zan_zamtyp,zan_rodzaj),0,0,zan_zamnumer,zan_zamrok,zan_zamseria,zan_zammiesiac) as [Dokument]
,[ZaE_TwrKod] as 'Kod_towaru'
,case when rez_ilosc IS NULL then 0 else rez_ilosc end as 'Ilość_do_realizacji'
,case when Z.Atr_Wartosc<>'' and Z.Atr_Wartosc<>'0' then convert(varchar(7),dateadd(d,convert(int,Z.Atr_Wartosc),'18001228'),121) else '' end as 'Planowana_data_dostawy'
FROM [xl_pst].[CDN].[ZamNag]
left join [xl_pst].[CDN].[ZamElem] on ZaE_GIDNumer = Zan_GIDNumer
left join [xl_pst].[CDN].[ZaNOpisy] on ZnO_ZamNumer = ZaN_GIDNumer
left join [xl_pst].[CDN].[Rezerwacje] on Rez_TwrNumer = ZaE_TwrNumer and Rez_KntNumer = ZaN_KntNumer and Rez_ZrdNumer = ZaN_GIDNumer
left join [xl_pst].[CDN].[Atrybuty] Z on Z.Atr_AtkId=39 and Z.Atr_ObiNumer = ZaN_GIDNumer
WHERE ZaN_ZamTyp = '1152' AND rez_ilosc > 0
ORDER
BY Planowana_data_dostawy ASC --ZaN_GIDNumer ASC
Kod: Zaznacz cały
DECLARE @miesiace NVARCHAR(MAX)
set @miesiace =
(
'
['+ CONVERT(VARCHAR(7),DATEADD(month, 0,GETDATE()), 120)+'], ['+ CONVERT(VARCHAR(7),DATEADD(month, 1,GETDATE()), 120)+'],
['+ CONVERT(VARCHAR(7),DATEADD(month, 2,GETDATE()), 120)+'], ['+ CONVERT(VARCHAR(7),DATEADD(month, 3,GETDATE()), 120)+'],
['+ CONVERT(VARCHAR(7),DATEADD(month, 4,GETDATE()), 120)+'], ['+ CONVERT(VARCHAR(7),DATEADD(month, 5,GETDATE()), 120)+'],
['+ CONVERT(VARCHAR(7),DATEADD(month, 6,GETDATE()), 120)+'], ['+ CONVERT(VARCHAR(7),DATEADD(month, 7,GETDATE()), 120)+'],
['+ CONVERT(VARCHAR(7),DATEADD(month, 8,GETDATE()), 120)+'], ['+ CONVERT(VARCHAR(7),DATEADD(month, 9,GETDATE()), 120)+'],
['+ CONVERT(VARCHAR(7),DATEADD(month, 10,GETDATE()), 120)+'], ['+ CONVERT(VARCHAR(7),DATEADD(month, 11,GETDATE()), 120)+']
'
)
PRINT (@miesiace);
DECLARE @COL NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DataOd varchar(MAX) , @DataDo varchar(MAX)
SELECT @DataOd = CONVERT(VARCHAR(20),DATEADD(month,-2,GETDATE()), 120)
, @DataDo = CONVERT(VARCHAR(20),DATEADD(month,-0,GETDATE()), 120)
SET @SQL =
'SELECT Kod_towaru,' +@miesiace+ '
FROM
(
SELECT a.Data, a.Kod_towaru, SUM(a.Ilość_do_realizacji) AS Ilosc
FROM
(
SELECT
[ZaE_TwrKod] as ''Kod_towaru''
,case when rez_ilosc IS NULL then 0 else rez_ilosc end as ''Ilość_do_realizacji''
,case when Z.Atr_Wartosc<>'' and Z.Atr_Wartosc<>''0'' then convert(varchar(7),dateadd(d,convert(int,Z.Atr_Wartosc),''18001228''),121) else '' end AS ''Data''
FROM [xl_pst].[CDN].[ZamNag]
left join [xl_pst].[CDN].[ZamElem] on ZaE_GIDNumer = Zan_GIDNumer
left join [xl_pst].[CDN].[ZaNOpisy] on ZnO_ZamNumer = ZaN_GIDNumer
left join [xl_pst].[CDN].[Rezerwacje] on Rez_TwrNumer = ZaE_TwrNumer and Rez_KntNumer = ZaN_KntNumer and Rez_ZrdNumer = ZaN_GIDNumer
left join [xl_pst].[CDN].[Atrybuty] Z on Z.Atr_AtkId=39 and Z.Atr_ObiNumer = ZaN_GIDNumer
WHERE ZaN_ZamTyp = ''1152'' AND rez_ilosc > 0
) AS a
GROUP BY a.Data, a.Kod_towaru
)
AS SourceTable
PIVOT (SUM(Ilosc) FOR Data IN (' +@miesiace+ '
)
)
AS PivotTable'
--PRINT (@SQL)
EXECUTE (@SQL)
[2022-07], [2022-08],
[2022-09], [2022-10],
[2022-11], [2022-12],
[2023-01], [2023-02],
[2023-03], [2023-04],
[2023-05], [2023-06]
Kod: Zaznacz cały
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '0'.
Ktoś, coś? Z góry dzięki za pomoc.