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);