I still couldn't understand what you're trying to point...
Multiplying the value gives me 3032, whereas I get 12 when divided...
I am curious to know, how the said value can be used in the context of the post?
if one would like to group items belonging to the same week, one can assign a unique number to all days of a particular week.
12/2/2007 3070 (Sunday, new week, new number)
12/9/2007 3071 (Sunday, new week, new number)
12/16/2007 3072 (Sunday, new week, new number)
So if I want all the items that are on the week 12/2/2007, then for the dates you are checking the formula should evaluate to 3070
(which you get by evaluating it for any day in the requested week, in this case '12/3/2007').
Generating a single number is just a convenience (or not). One can find a particular week by:
DATEPART( wk, somedayfromtable ) = DATEPART( wk, '3/12/2007' ) AND (DATEPART( yyyy, somedayfromtable ) = (DATEPART( yyyy, '3/12/2007' )
The above will get the items that belong to a particular week (the week for '3/12/2007'), the single number is equivalent
to the above statement (it just maps the year and week pair to a number).
Anyway, the above approach is useful if you would like to group by rows (which is the way I mis?understood the question
Your solution is also better for the original question from the point of view that it calculates the start and end date, and then it uses only datetime comparison, no need for calculations (or breaking up the datetime to a year and week number part)
Thanks for correcting me
Andras Belokosztolszki, MCPD, PhD