Anyone can join in if they want to:

I'm avoiding the word challenge, because there's no such thing as a "winner" here. Even if one approach might somehow appear to be "better", "earier to understand", a person might be in a situation where alternate approaches might suit them better.



Suppose we have an order table with 3 columns:

VendorID (int)
OrderDate (Date)
TotalDue (decimal)


We want to produce a result set, that summarizes orders by week-ending date (Saturday), for any date range passed as parameters. The result set should produce 1 row for each combination of vendor account/Week-ending Date, regardless of whether orders existed for that vendor/week. (Let's say the result set is being used for a weekly chart by vendor account)

So...suppose someone passes a date range of 1-1-2004 to 3-31-2004 - the process should retrieve data for the earliest possible Sunday (12-28-2003) to the latest possible Saturday (4-3-2004) and summarize orders by vendor account/week ending date.

Suppose we just have two rows:

VendorID 1
OrderDate 2-3-2008
TotalDue $100


VendorID 1
OrderDate 2-4-2008
TotalDue $200

the result set would be

WeekEnding    VendorID   TotalDue
1-3-2004      1          0
1-10-2004     1          0
1-17-2004     1          0
1-24-2004     1          0
1-31-2004     1          0
2-7-04        1          300
2-14-04       1          0

etc.

(but assume there are many vendors in the table)



Thanks,
Kevin