Loops Temp Tables

I need help creating the following report.

I need to modify the folliwng report to produce a weekly version of it.
To get a weekly version, simply find the first day of the week for the @dtm1 value :

should be something like @firstofweek=dateadd(day,(datepart(dw,@dtm1)*-1)+1,@dtm1) to get the Sunday date

I think i will then then need to loop through from firstofweek to last of week (last of week will be the Saturday)

would suggest having a dayofweek column on your temporary table.



CREATE PROCEDURE rpt_siteMealListWeekly


@dtm1 AS DATETIME

,@cmb1 AS VARCHAR(100)

WITH ENCRYPTION

AS


--DECLARE @dtm1 AS DATETIME


DECLARE @siteid as integer

SELECT @siteid=siteid from site where sitename=@cmb1

--SELECT @dtm1='2007-09-13',@siteid=1

--select convert (char(11),@dtm1,113)


DECLARE @mybit AS INTEGER

SET @mybit=10

SELECT @mybit = CASE datepart(dw,@dtm1)


WHEN 1 THEN 1 -- 'Sunday'

WHEN 2 THEN 2 -- 'Monday'

WHEN 3 THEN 4 -- 'Tuesday'

WHEN 4 THEN 8 -- 'Wednesday'

WHEN 5 THEN 16 -- 'Thursday'

WHEN 6 THEN 32 -- 'Friday'

WHEN 7 THEN 64 -- 'Saturday'

END

CREATE TABLE #tmp_table(


childid INTEGER null

,br BIT null

,di BIT null

,te BIT null

,type integer default 0

)

INSERT #tmp_table


SELECT DISTINCT sa.childid

,CASE WHEN sha.br & @mybit>0 THEN 1 ELSE 0 END

,CASE WHEN sha.di & @mybit>0 THEN 1 ELSE 0 END

,CASE WHEN sha.te & @mybit>0 THEN 1 ELSE 0 END

,0

FROM

sessionAttendance sa

,simplehoursassignment sha

,session s

,child c

WHERE


sha.childid=sa.childid

AND

sha.siteid=sa.siteid

AND

s.siteid=sa.siteid

AND

c.siteid = sa.siteid

AND

c.childid = sa.childid

AND

sa.siteid=@siteid

AND

s.identityid=sa.identityid

AND

s.dayofweek=datepart(dw,@dtm1)

AND

s.siteid=sa.siteid

AND

@dtm1 between cast(floor(cast(sa.datefrom as float))as smalldatetime) and cast(floor(cast(sa.dateto as float))as smalldatetime)

AND

sa.userdefid=0

AND

(

--check not a company holiday

not exists

(select

1

from

companyholidays ch

where

siteID=@siteID

and

@dtm1 between cast(floor(cast(ch.datefrom as float))as smalldatetime)

and

cast(floor(cast(ch.dateto as float))as smalldatetime)

)

)

and

(

sa.childid in