Thanks for your replay and your solutions.

I had invited a colleague to see how we can build like you suggested that meets our requirements, so we build it like this query.

Only when I try to select more than 1 value for my parameter @BUname in my report, then I'm getting the following error:

"column name or number of supplied values does not match table definition"

Do you have any idea what's wrong with it?

Or do you have an solution for this?

declare @tijdvakstart datetime

declare @tijdvakeind datetime

--declare @BUname varchar(250)

set @tijdvakstart= @StartDate

set @tijdvakeind= getdate()

declare @inassignement table ( consultant uniqueidentifier, dag datetime, available bit)

declare @bu_consultants table( consultant uniqueidentifier primary key, bu varchar(100) )

insert into @bu_consultants

select stex_consultant, @BUname from Filteredstex_consultantcontract

where stex_businessunitname IN (@BUname)

group by stex_consultant;

with mycte as

(

select cast(@tijdvakstart as datetime) DateValue

union all

select DateValue + 1

from mycte

where DateValue + 1 < @tijdvakeind)

insert into @inassignement

select CS_A.stex_consultant,DateValue,1 AS INASSIGNMENT

--CS_A.stex_startdate , CS_A.stex_enddate

--CS_A.stex_startdate,CS_A.stex_enddate

from mycte

LEFT JOIN Filteredstex_assignmentdetail CS_A

ON

-- the day of DateValue in Assignement ?

( DATEVALUE BETWEEN CS_A.stex_startdate AND CS_A.stex_enddate OR ( CS_A.stex_enddate IS NULL AND CS_A.stex_startdate <= DateValue ))

WHERE CS_A.stex_businessunitname IN (@BUname)

and CS_A.statuscode = 1

group by DateValue,CS_A.stex_consultant

OPTION (MAXRECURSION 0);

with mycte as

(select cast(@tijdvakstart as datetime) DateValue

union all

select DateValue + 1

from mycte

where DateValue + 1 < @tijdvakeind)

select

DATEPART(YEAR, DATEVALUE) AS Jaar,

SUM((( CASE WHEN ( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR ( CS.stex_enddate IS NULL AND CS.stex_startdate <= DateValue) ) THEN 1 ELSE 0 END )))

AS InDienst,

SUM(((CASE WHEN nb.dag is null

AND

-- in Employed?

( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR ( CS.stex_enddate IS NULL AND CS.stex_startdate <= DateValue) )

THEN 1 ELSE 0 END) )) AS Available,

DATEPART(WEEK, DateValue ) AS Weeknummer, 0

from mycte

LEFT OUTER JOIN Filteredstex_consultantcontract CS

ON ( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR CS.stex_enddate IS NULL)

LEFT OUTER JOIN @inassignement nb ON nb.consultant = CS.stex_consultant AND dag=DateValue

WHERE CS.stex_consultant IN ( select consultant from @bu_consultants )

GROUP BY DATEPART(YEAR, DATEVALUE) , DATEPART(WEEK, DateValue )

ORDER BY DATEPART(WEEK, DateValue ) ASC

OPTION (MAXRECURSION 0);