Sergey

I think this works, I'm going to try it with real data, but I thought I'd see if you saw any issues with it?


declare @dt datetime
declare @dt1 datetime
declare @dt2 datetime
declare @dt3 datetime
declare @dt4 datetime
declare @dt5 datetime

set @dt1 = '06/01/2007' -- First Week
set @dt2 = '06/04/2007' -- Second Week
set @dt3 = '06/11/2007' -- Third Week
set @dt4 = '06/18/2007' -- Fourth Week
set @dt5 = '06/25/2007' -- Fifth Week

select
ceiling(cast((cast(DAY(@dt1) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week1,
ceiling(cast((cast(DAY(@dt2) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week2,
ceiling(cast((cast(DAY(@dt3) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week3,
ceiling(cast((cast(DAY(@dt4) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week4,
ceiling(cast((cast(DAY(@dt5) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week5

Week1 Week2 Week3 Week4 Week5
=================================== =======
1 2 3 4 5