I had to change my (insert into) for previous error, but now having this error:
"The select list for the INSERT statement contains more items than the insert list.
The number of SELECT values must match the number of INSERT columns. "
I'm probably missing something and I don't know what...
my query:
________________________________________________________________
declare @tijdvakstart datetime
declare @tijdvakeind datetime
--declare @BUname varchar(100)
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 (consultant, bu)
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 (consultant, 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
-- 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.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);