-- 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)

-- Number of days from 1st Jan:
declare @FirstDayOfYear Date, @DateOffset int;
set @FirstDayOfYear = DATEADD(year,DATEDIFF(year,0,@StartDate),0); -- Date of First Day of Calendar Year
set @DateOffset = datediff(dd, @FirstDayOfYear, @StartDate) -- Difference between start of Calendar & Fiscal Years

--print @DateInt
--print @DateEndInt
--print @DateShift

-- Create date table with Date & Fiscal Week:
declare @a as table ( [Date] Date, FiscalWeek Int );

while @DateInt < @DateEndInt
begin
	-- Insert next Date, & week number subtracting offset to get correct fiscal week
	insert into @a values ( dateadd(dd, 0, @DateInt), datepart(ww, dateadd(dd, (-@DateOffset), @DateInt)) );
	set @DateInt = @DateInt +1;
end

select * from @a;

-- 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;

DECLARE @StartDT    DATETIME = '2011-04-01'

;WITH Dates AS (
    SELECT TOP (1+DATEDIFF(day, @StartDT, DATEADD(day, -1, DATEADD(year, 1, @StartDT))))
        d=DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @StartDT)
    FROM sys.all_columns)
SELECT d, Day=DATENAME(dw, d)
    ,Week=CASE WHEN DATEPART(ww, d) <= 13 THEN 40+DATEPART(ww, d) ELSE DATEPART(ww, d) - 13 END
FROM Dates