TSQL select last value by week?

Question

I have the following query:

SELECT  row_ID,
        value,
        date_added
FROM    dbo.Report
WHERE   salesman_ID = @salesman_ID
        AND row_ID IN ( SELECT  MAX(row_ID)
                        FROM    dbo.Report
                        WHERE   salesman_ID = @salesman_ID
                        GROUP BY ( date_added ) )
ORDER BY date_added ASC 

This selects the last value entered on each day for a given user (the date_added column doesn't contain time values, and the row IDs are autonumbered). The query works, but what I'd like to do is select the last value entered for a given calendar week rather than a given day.

For example, here's what the current query returns:

row_ID | value | date_added
1      |   25  | 7-5-2010
2      |   50  | 7-7-2010
5      |   40  | 7-15-2010
9      |   55  | 7-16-2010
11     |   60  | 7-27-2010 

Here's what I'd like it to return:

value | week_of
 50   | 7-4-2010
 55   | 7-11-2010
 60   | 7-22-2010 

Any ideas?

Answer 1

This will give you the first and last day for the week where a day falls in

DECLARE @d DATETIME
SET @d = '20100805'

SELECT

    @d - DATEDIFF(dd, @@DATEFIRST - 1, @d) % 7 AS FirstDayOfWeek,
    @d - DATEDIFF(dd, @@DATEFIRST - 1, @d) % 7 + 6 AS LastDayOfWeek

just incorporate that into your subquery

Answer 2

I think if you replace your original GROUP BY clause with

GROUP BY (DATEPART(WEEK,date_added))

you should get what you want.