Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013

    Unanswered: Update Statement for 13 week Avg

    Hey Guys,

    Hope you guys can help me figure out the correct update statement syntax for the following integration.

    I have a "Performance Table" which i insert weekly performance numbers into for each store. The table is constructed w/ columns such as Store, Weekenddate, Sales, Refunds, #ofPatients

    In a "Averages Table" i have every weekenddate for each store populated. So 52 Weeks for 10 stores = 520 Rows of Store numbers & WeekendDates.

    What i would like to do is run a loop or update statement which would update the store average for each weekendate based on the last 13 weeks.

    This is my query

    update performancestore_avgs set SalesAvg =
    (select sum(SalesHit)/Count(Store) from performance_store where performance_store.weekenddate >= performancestore_avgs.weekenddate-84 and =

    The update statement runs but the averages are completly wrong. Please help...

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    group by the weekenddate?

  3. #3
    Join Date
    Apr 2012

    shelanp07, I do not know if I got it wrong, but I believe that Count(Store) will always equal 1. The correct would be divided by 13?
    I also believe you're missing the date limit until performancestore_avgs.weekenddate.

    Hope this helps.

  4. #4
    Join Date
    Feb 2013
    thanks Imex. I kept my query as is but added the date limit as you said w/ a "between" syntax. The data looks spot on! Thanks a bunch guys

Posting Permissions

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