Unanswered: Help: query to get total last 7 day sales for each day
Let say I have this table in MS SQL server
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
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)
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.