Try this:

-- Start & End of Fiscal Year:

declare @StartDate Date, @DateInt int, @DateEndInt Int;

set @StartDate = '01 Apr 2012'; -- Start Date of Fiscal Year

set @DateInt = datediff(dd, 0, @StartDate); -- Start Date as int

set @DateEndInt = datediff(dd, 0, dateadd(yy, 1, @StartDate)); -- End Date as int (+1 day)

-- Create date table with Date & Fiscal Week:

declare @a as table ( [Date] Date, DayName Varchar(10), FiscalWeek Int );

declare @Start int;

set @Start = @DateInt

-- Populate table:

while @DateInt < @DateEndInt

begin

insert into @a values ( dateadd(dd, 0, @DateInt), DATENAME(weekday,dateadd(dd, 0, @DateInt)), ((@dateInt-@Start)/7)+1 );

set @DateInt = @DateInt +1;

end

select * from @a;