I have the following query to count the number of employees per costcenter using the pivot command.
The @Columns parameter declares these costcenters.
The query shows results but the problem is that the count is zero for every costcenter! When I do "count(*)", it has the same problem.
Even a night of proper sleep didn't brought up a light.
What's wrong with the count in the pivot command?
I think it's simple quite but I haven't figured it out yet ...
Thanks for your help!
declare @Columns varchar(max)
declare @Query nvarchar(max)
select @Columns = isnull(@Columns + ',[' + kstplcode + ']', '[' + kstplcode + ']')
where LEFT(kstplcode, 1) in ('B', 'S')
set @Query =
declare @DaysMinus int
set @DaysMinus = 8
select ''No of employees per '' + convert(varchar(10), PeriodEnd, 105) as KPI,
' + @Columns + '
select h.res_id as Resource,
pd.eddatum as PeriodEnd,
kp.oms25_0 as Costcenter
from humres 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
where isnull(h.ldatuitdienst, getdate()+1) > pd.eddatum - @DaysMinus - and h.res_id > 5000
) as data
pivot (count(data.Resource) for data.Costcenter in ('+ @Columns +')) as pivottable