Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    5

    Unanswered: 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 chua

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i believe you can do it in a simpler way. can you post some sample data and the output you are looking for?

  3. #3
    Join Date
    Jan 2006
    Posts
    5
    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--);

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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'

  5. #5
    Join Date
    Jan 2006
    Posts
    5

    Thumbs up

    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
    regards
    terence chua

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •