Any ideas why i get an error in my script.
set dateformat YMD
set datefirst 7
CREATE TABLE #ChildSessions (
siteid integer null
,childid integer null
,sessionid integer null
,sun integer default 0
,mon integer default 0
,tue integer default 0
,wed integer default 0
,thr integer default 0
,fri integer default 0
,sat integer default 0)
declare @firstofweek as datetime
declare @lastofweek as datetime
--select datepart(dw,getdate())
select @firstofweek=cast(floor(cast(dateadd(day,(-1*datepart(dw,getdate())+1),getdate()) as float)) as datetime)
select @lastofweek=dateadd(minute,-1,dateadd(day,7,@firstofweek))
declare @myday integer
set @myday=0
while @myday<7
BEGIN
INSERT INTO #childSessions
SELECT
c.siteid
,c.childid
,sg.sessionid
,case @myday WHEN 1 THEN 1 ELSE 0 end
,case @myday WHEN 2 THEN 1 ELSE 0 end
,case @myday WHEN 3 THEN 1 ELSE 0 end
,case @myday WHEN 4 THEN 1 ELSE 0 end
,case @myday WHEN 5 THEN 1 ELSE 0 end
,case @myday WHEN 6 THEN 1 ELSE 0 end
,case @myday WHEN 7 THEN 1 ELSE 0 end
FROM
child c
,sessionAttendance sa
,session s
,sessiongroup sg
WHERE
c.childID = sa.childid
AND c.siteid = sa.siteid
AND c.active = 1
AND c.potential = 0
AND s.identityid = sa.identityid
AND s.siteid = sa.siteid
AND sg.sessionid = s.sessionID
AND sg.siteid = s.siteid
AND s.dayofweek = @myday
AND @firstofweek <= sa.dateTo
AND @lastofweek >= sa.dateFrom
SET @myday=@myday+1
END
SELECT
c.forename,
c.surname,
sg.sessionname,
CASE (sum(sun)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(mon)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(tue)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(wed)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(thr)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(fri)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(sat)) WHEN 0 THEN ' ' ELSE 'X'
FROM child c,sessiongroup sg,#childsessions cs
WHERE c.childid=cs.childid
AND c.siteid=cs.siteid
AND sg.sessionid=cs.sessionid
AND sg.siteid=cs.siteid
GROUP BY c.forename,c.surname,sg.sessionname
ORDER BY sg.sessionname,c.forename,c.surname
DROP TABLE #childsessions
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near ','.