Hi all

I have a table name Fiscal Qtr, in this table basically i am storing the quarter data.

CREATE TABLE [dbo].[FISCAL_QTR](
[FISCAL_QTR_ID] [int] NOT NULL,
[FISCAL_QTR] [int] NOT NULL,
[FISCAL_YEAR] [int] NOT NULL,
[DESCR] [varchar](30) NOT NULL,
[START_DATE] [datetime] NOT NULL,
[END_DATE] [datetime] NOT NULL
) ON [PRIMARY]
this is my table
and the data's in this table is like this
01,1,2010,2-2010,11/2/2009 12:00:00 AM,1/31/2010 12:00:00 AM
02,2,2010,2-2010,2/1/2010 12:00:00 AM,5/2/2010 12:00:00 AM
03,3,2010,3-2010,5/3/2010 12:00:00 AM,8/1/2010 12:00:00 AM
04,4,2010,4-2010,8/2/2010 12:00:00 AM,10/31/2010 12:00:00 AM

so this is my script
CREATE CLUSTERED INDEX id ON dbo.FISCAL_QTR(FISCAL_QTR_ID)
GO

Declare @year int
Set @year = '2011'
While @year <= '2015'
Begin

Declare @id int
Declare @Qtr int
Declare @startDate datetime
Declare @enddate datetime

SET @id = (SELECT (Max(FISCAL_QTR_ID))
From dbo.FISCAL_QTR)
Set @startdate =(Select DATEADD(day,1,END_DATE)FROM dbo.FISCAL_QTR
where FISCAL_QTR_ID=@id)
Set @enddate =(Select DATEADD(Week,13,END_DATE)FROM dbo.FISCAL_QTR
where FISCAL_QTR_ID=@id)

INSERT INTO dbo.FISCAL_QTR(FISCAL_QTR_ID,DESCR,START_DATE,END_DATE)
VALUES ((@id+1),@year,@startdate,@enddate)

SET @year = @year + 1
END

drop index id on dbo.FISCAL_QTR

which is incomplete and i dont know how to proced.
Since my quarter formate is 4-4-5 weeks.
Please help me in this

I don't think you need a loop here at all.  You can also make START_DATE and END_DATE SMALLDATETIME and save 8 bytes per row and probably make some queries more efficient too.

DELETE  dbo.FISCAL_QTR;

INSERT dbo.FISCAL_QTR
SELECT
        [FISCAL_QTR_ID] = ROW_NUMBER() OVER (ORDER BY dt),       
        [FISCAL_QTR] = x + 1,
        [FISCAL_YEAR] = y,
        [DESCR] = RTRIM(x + 1) + '-' + RTRIM(y),
        [START_DATE] = dt,
        [END_DATE] = DATEADD(MONTH, 3, dt) - 1
    FROM
    (
        SELECT
            dt = DATEADD(MONTH, (3*x), RTRIM(y) + '0101'),
            y,
            x
        FROM
        (
            SELECT x = 0
            UNION SELECT 1
            UNION SELECT 2
            UNION SELECT 3
        ) x
        CROSS JOIN
        (
            SELECT y = 2010
            UNION SELECT 2011
            UNION SELECT 2012
            UNION SELECT 2013
            UNION SELECT 2014
            UNION SELECT 2015
        ) y
    ) z
    ORDER BY CONVERT(SMALLDATETIME, dt);

DELETE dbo.FISCAL_QTR;

INSERT dbo.FISCAL_QTR 
SELECT
        [FISCAL_QTR_ID] = ROW_NUMBER() OVER (ORDER BY dt),        
        [FISCAL_QTR] = x + 1,
        [FISCAL_YEAR] = y,
        [DESCR] = RTRIM(x + 1) + '-' + RTRIM(y),
        [START_DATE] = dt,
        [END_DATE] = DATEADD(MONTH, 3, dt) - 1
    FROM
    (
        SELECT
            dt = DATEADD(MONTH, (3*x), RTRIM(y) + '0101'),
            y,
            x
        FROM
        (
            SELECT x = 0
            UNION SELECT 1
            UNION SELECT 2
            UNION SELECT 3
        ) x
        CROSS JOIN
        (
            SELECT y = 2010
            UNION SELECT 2011
            UNION SELECT 2012
            UNION SELECT 2013
            UNION SELECT 2014
            UNION SELECT 2015
        ) y
    ) z
    ORDER BY CONVERT(SMALLDATETIME, dt);