Ok thanks for your suggestions, it's fixed now. The report shows what we want.

But the report is not performing like we want, this is something that we have to look at.

Thanks...

query:

____________________

declare @tijdvakstart datetime

declare @tijdvakeind datetime

--declare @BUname varchar(100)

set @tijdvakstart=@StartDate

set @tijdvakeind= getdate();

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

declare @bu_consultants table( consultant uniqueidentifier primary key )

insert into @bu_consultants ([consultant])

select stex_consultant from Filteredstex_consultantcontract

where stex_businessunitname IN (@BUname)

and not stex_consultant is null

group by stex_consultant;

--print 'Consultants wel in assigmentdetail maar niet in contract'

--select stex_consultant from Filteredstex_assignmentdetail ad where ad.stex_consultant not in (select stex_consultant from Filteredstex_consultantcontract CS

--where CS.stex_businessunitname = @BUname)

--and ad.stex_businessunitname = @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 (consultantt, dag, available)

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

-- 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--, CS_A.stex_startdate, CS_A.stex_enddate

--order by DateValue, CS_A.stex_consultant, CS_A.stex_startdate, CS_A.stex_enddate

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.consultantt = 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);