Unanswered: Insert record into temporary table from a select statement
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?
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).
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
group by case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end