Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Help: query to get total last 7 day sales for each day

    Let say I have this table in MS SQL server
    table transaction(date,sales)

    how to query to get result like this (date,sales,sum(sales last 7 day))
    I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

    note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day


  2. #2
    Join Date
    Feb 2004
    select date,sum(sales) from table where date between (getdate()-7) and getdate()
    group by date

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    That will get you sales within the last 7x24 hours, because getdate returns the full date and time. Maybe that's what you want, but if you are looking at full sales days you should try the datediff function:

    select convert(char(10), date, 120), sum(sales)
    from table
    where datediff(yourdate, getdate()) < 7 --(or 6, depending upon whether you want to count the current day)
    group by convert(char(10), date, 120)
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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