Hi Ramesh,

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.

For example:

12/2/20073070 (Sunday, new week, new number)

12/3/20073070

12/4/20073070

12/5/20073070

12/6/20073070

12/7/20073070

12/8/20073070

12/9/20073071 (Sunday, new week, new number)

12/10/2007 3071

12/11/20073071

12/12/20073071

12/13/20073071

12/14/20073071

12/15/20073071

12/16/20073072 (Sunday, new week, new number)

12/17/20073072

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 :blush: ). Sorry.

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