Insert record into temporary table from a select statement

Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" ... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chuai believe you can do it in a simpler way. can you post some sample data and the output you are looking for?|||here is the sample data. i want to group them to be able to use in 3 record in this case.

weekgroup mark updatedate
Week 1 100.000000 2006-01-03 09:37:15.000
Week 1 100.000000 2006-01-06 12:18:09.000
Week 1 71.600000 2006-01-06 12:59:46.000
Week 1 100.000000 2006-01-06 13:03:52.000

Week 2 95.000000 2006-01-09 11:49:17.000
Week 2 100.000000 2006-01-09 12:19:19.000

Week 3 100.000000 2006-01-16 15:03:24.000
Week 3 71.600000 2006-01-16 15:05:31.000
Week 3 100.000000 2006-01-17 15:59:43.000
Week 3 100.000000 2006-01-17 16:57:38.000

--------
here is the code i did. i set the @.dtstart = 1st day of the month
@.dtWeekEnd = 1 week after the 1st day of the month
@.dtEnd = end of the month

i try to group by the weekgroup but it fail. and it only allow to group by the last update date(Answer.dtAnswer).

SELECT CASE
WHEN day(@.dtStart) <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) THEN 'Week 1'
WHEN day(@.dtStart)+7 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 7 THEN 'Week 2'
WHEN day(@.dtStart)+14 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 14 THEN 'Week 3'
WHEN day(@.dtStart)+21 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 21 THEN 'Week 4'
else 'Week 5'
END as weekgroup, AVG(Answer.bScore) AS tScore, dtAnswer -- CONVERT(INT, AnswerKey.bRowId) AS bRowId-- day(Answer.dtAnswer) as days
FROM Answer INNER JOIN
AnswerKey ON Answer.lAnswerId = AnswerKey.lAnswerId INNER JOIN
QuestionDef ON AnswerKey.iQuestionDefId = QuestionDef.iQuestionDefId INNER JOIN
QuestionAnswerDef ON QuestionDef.iQuestionDefId = QuestionAnswerDef.iQuestionDefId AND AnswerKey.bKeyId = QuestionAnswerDef.bKeyId
WHERE (Answer.iMinutes BETWEEN 570 AND 1020) and (Answer.dtAnswer >= @.dtStart) AND (Answer.dtAnswer < @.dtEnd) and (Answer.iTemplateId = 1)
GROUP BY Answer.iTemplateId, AnswerKey.bRowId, QuestionDef.sQuestion, Answer.fDiscard,
QuestionAnswerDef.sAnswer, Answer.fIncomplete, AnswerKey.bScore, AnswerKey.bRowId, dtAnswer--, WeekGroup--, NoOfWeek
having (Answer.fDiscard = 0) AND (Answer.fIncomplete = 0)
order by dtAnswer--);|||what i understand, from the long sql, is you have a table with a numeric col and a date col and you want to have a AVG of the numeric col on at an interval of every 7 days, staring from the first day of the month...

an example to do the above for 1st half and 2nd half of the month could be

select avg(<Numeric Col>),
case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end
from table2
group by case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end

here the first date of the month is '20060101'|||thank for your advice, i found a solution from your sample.

i dun know using the case also can put at group by so problem solve easily.

thanks a lot:D
regards
terence chua