Getting dates for reports by week, etc.

[dannod]

I am doing sales reports with straight SQL queries and I was wondering if there was a way to get the dates of all the Mondays when we are grouping by the week for sales reports. The problem is that not every Monday has sales, so when we do a group by DAYOFWEEK, the first day of that week where there was a sale is the date that shows up, and not that particular Monday. For example, say I ran this report for January 2003 we want it to look like this:

1/6/2003: $12,039
1/13/2003: $9,510
1/20/2003: $15,111
1/27/2003: $6,000

But if there are no sales on 1/6/2003 and the first sale for that week was 1/8/2003, then the report looks like this:

1/8/2003: $12,039
1/13/2003: $9,510
1/20/2003: $15,111
1/27/2003: $6,000

Same goes for any other week -- say there were no sales the week of 1/13 until the 16th, then the report would look like this:

1/8/2003: $12,039
1/16/2003: $9,510
1/20/2003: $15,111
1/27/2003: $6,000

The query I have migrated to right now after futzing with this forever is below. I realize that MIN(ORDERPLACED) is not going to grab the 1st date of the week, so I need to replace that with something -- I just don't know what.

SELECT sum(ordertotal) as total, min(orderplaced) as date, YEARWEEK(orderplaced) as grouping FROM orders group by grouping order by date asc

 

[r937]

see if this article helps any --

Grouping output by week

http://tinyurl.com/4ble

rudy