Thanks for your reply!

I do something wrong but not sure what ...

I think the @SumColumns variable is not correct.

Hope you can help me out? Thanks

Here is my query:

DECLARE @Columns VARCHAR(MAX)

DECLARE @SumColumns VARCHAR(MAX)

DECLARE @Query nVARCHAR(MAX)

SELECT @Columns = ISNULL(@Columns + ',[' + kstplcode + ']', '[' + kstplcode + ']')

FROM kstpl

WHERE Enabled = 1

SELECT @SumColumns = ISNULL(@Columns + ',[' + @Columns + ']', '[' + @Columns + ']')

FROM kstpl

WHERE Enabled = 1

SET @Query =

N'

DECLARE @DaysMinus INT

SET @DaysMinus = 18;

--WITH BasePivot as

(

SELECT''No of employees per '' + convert(VARCHAR(10), PeriodEnd, 105) as KPI,

' + @Columns + '

FROM

(

SELECTh.res_id as Resource,

pd.eddatum as PeriodEnd,

h.costcenter as Costcenter

--kp.oms25_0 as CComs

FROMhumres h

LEFT OUTER JOIN perdat pd ON convert(VARCHAR(10), GETDATE()- @DaysMinus, 105) = convert(VARCHAR(10), pd.eddatum, 105)

LEFT OUTER JOIN kstpl kp ON h.costcenter = kp.kstplcode

WHEREISNULL(h.ldatuitdienst, getdate()+1) > pd.eddatum - @DaysMinus /* Job runs on the last day of period */

AND h.res_id > 5000

GROUP BY pd.eddatum, h.costcenter, h.res_id

) as data

PIVOT(count(data.Resource) FOR data.Costcenter IN (' + @Columns + ')) as pivottable

)

SELECT

--KPI, ' + @Columns + ' FROM BasePivot

KPI, ' + @Columns + ', ' + @SumColumns + ' FROM pivottable

'

EXEC (@Query)