DECLARE @startweek datetime

DECLARE @eindweek datetime

SET @startweek= (select dbo.get_week_start(getdate()+4))
SET @eindweek= (select dbo.get_week_end(getdate()+4))

select  CASE datename(weekday,a.startdate) 

  WHEN 'Monday'   THEN 'Maandag'

  WHEN 'Tuesday'   THEN 'Dinsdag'

  WHEN 'Wednesday' THEN 'Woensdag'

  WHEN 'Thursday'  THEN 'Donderdag'

  WHEN 'Friday'    THEN 'Vrijdag'

  WHEN 'Saturday'  THEN 'Zaterdag'

  WHEN 'Sunday'    THEN 'Zondag'

 END as dag,

 SUBSTRING(CONVERT(varchar(30),a.startdate,120),12,5) as starttijd,

 SUBSTRING(CONVERT(varchar(30),a.enddate,120),12,5) as eindtijd,

at.description AS Type,

a.description AS Omschrijving,

h.fullname,

p.description AS Schip,

hid,

'{' + CAST(a.ID AS varchar(50)) + '}' AS reqid,

ISNULL(CASE freetextfield_01
WHEN 'extern' THEN 'Extern'
WHEN 'intern' THEN 'Intern'
END, '') AS waar,

 CASE WHEN DATEDIFF(dd,a.startdate,a.enddate)>1 THEN CONVERT(varchar(20),a.startdate,105)+' tm '+ CONVERT(varchar(20),a.enddate,105) ELSE CONVERT(varchar(20),a.startdate,105) END as datum

from absences a 

join absencetypes at ON at.ID=a.type AND (at.UseInCalendar=1 or at.PlanHumanResource=1)

left join humres h ON h.res_id=a.EmpID

LEFT JOIN prproject p ON p.projectnr = a.projectnumber


where  

 a.startdate BETWEEN @startweek AND @eindweek OR

 a.enddate BETWEEN @startweek AND @eindweek OR

 (a.startdate<@startweek AND a.enddate>@eindweek)

order by startdate