-- =============================================
-- Author:		Исхаков Тимур
-- Create date: 26.02.2009
-- Description:	Заполняет таблицу периодов на указанные годы
-- =============================================
        TRUNCATE TABLE [dbo].[Periods]

declare    @startDate datetime
declare    @endDate datetime

set @startDate = '2013-08-05 00:00:00.000'
set @endDate = '2013-09-04 23:59:59.000'

    BEGIN

        DECLARE @i INT
        DECLARE @week_count INT
        DECLARE @decade_count INT
        DECLARE @time VARCHAR(9)

        SET @time = ' 23:59:59'



        INSERT  INTO periods
                ( PeriodName ,
                    PeriodFullName ,
                    PeriodTypeID ,
                    PeriodYear ,
                    PeriodSortNum
                )
        VALUES  ( 'Year' ,
                    Year(@startDate) ,
                    1 ,
                    Year(@startDate) ,
                    Year(@startDate)
                )

        INSERT  INTO periods
                ( PeriodName ,
                    PeriodFullName ,
                    PeriodTypeID ,
                    PeriodYear ,
                    PeriodQuarter ,
                    PeriodSortNum
                )
        VALUES  ( '1 квартал' ,
                    STR(Year(@startDate), 4) + ' 1 квартал' ,
                    3 ,
                    Year(@startDate) ,
                    1 ,
                    1
                )

        INSERT  INTO periods
                ( PeriodName ,
                    PeriodFullName ,
                    PeriodTypeID ,
                    PeriodYear ,
                    PeriodQuarter ,
                    PeriodSortNum
                )
        VALUES  ( '2 квартал' ,
                    STR(Year(DATEADD(quarter, 1, @startDate)), 4) + ' 2 квартал' ,
                    3 ,
                    Year(DATEADD(quarter, 1, @startDate)) ,
                    2 ,
                    2
                )

        INSERT  INTO periods
                ( PeriodName ,
                    PeriodFullName ,
                    PeriodTypeID ,
                    PeriodYear ,
                    PeriodQuarter ,
                    PeriodSortNum
                )
        VALUES  ( '3 квартал' ,
                    STR(Year(DATEADD(quarter, 2, @startDate)), 4) + ' 3 квартал' ,
                    3 ,
                    Year(DATEADD(quarter, 2, @startDate)) ,
                    3 ,
                    3
                )

        INSERT  INTO periods
                ( PeriodName ,
                    PeriodFullName ,
                    PeriodTypeID ,
                    PeriodYear ,
                    PeriodQuarter ,
                    PeriodSortNum
                )
        VALUES  ( '4 квартал' ,
                    STR(Year(DATEADD(quarter, 3, @startDate)), 4) + ' 4 квартал' ,
                    3 ,
                    Year(DATEADD(quarter, 3, @startDate)) ,
                    4 ,
                    4
                )


        UPDATE  Periods
        SET     DateBegin = @startDate,
                DateEnd = @endDate
        WHERE   PeriodTypeId = 1

		-- если период целый год
        UPDATE  Periods
        SET     DateBegin = @startDate,
                DateEnd = dbo.AddQuarter(@startDate)
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 1 AND dbo.AddQuarter(@startDate) <= @endDate

		UPDATE  Periods
        SET     DateBegin = DATEADD(quarter, 1, @startDate),
                DateEnd = dbo.AddQuarter(DATEADD(quarter, 1, @startDate))
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 2 AND dbo.AddQuarter(DATEADD(quarter, 1, @startDate)) <= @endDate

		UPDATE  Periods
        SET     DateBegin = DATEADD(quarter, 2, @startDate),
                DateEnd = dbo.AddQuarter(DATEADD(quarter, 2, @startDate))
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 3 AND dbo.AddQuarter(DATEADD(quarter, 2, @startDate)) <= @endDate

	   	UPDATE  Periods
        SET     DateBegin = DATEADD(quarter, 3, @startDate),
                DateEnd = dbo.AddQuarter(DATEADD(quarter, 3, @startDate))
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 4 AND dbo.AddQuarter(DATEADD(quarter, 3, @startDate)) <= @endDate

		-- если период не целый год
        UPDATE  Periods
        SET     DateBegin = @startDate,
                DateEnd = @endDate
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 1 AND dbo.AddQuarter(@startDate) > @endDate

		UPDATE  Periods
        SET     DateBegin = CASE WHEN DATEADD(quarter, 1, @startDate) > @endDate THEN @endDate ELSE dbo.AddQuarter(@startDate) END,
                DateEnd = CASE WHEN dbo.AddQuarter(DATEADD(quarter, 1, @startDate)) > @endDate THEN @endDate ELSE dbo.AddQuarter(DATEADD(quarter, 1, @startDate)) END
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 2 AND dbo.AddQuarter(DATEADD(quarter, 1, @startDate)) > @endDate

		UPDATE  Periods
        SET     DateBegin = CASE WHEN DATEADD(quarter, 2, @startDate) > @endDate THEN @endDate ELSE dbo.AddQuarter(DATEADD(quarter, 1, @startDate)) END,
                DateEnd = CASE WHEN dbo.AddQuarter(DATEADD(quarter, 2, @startDate)) > @endDate THEN @endDate ELSE dbo.AddQuarter(DATEADD(quarter, 2, @startDate)) END
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 3 AND dbo.AddQuarter(DATEADD(quarter, 2, @startDate)) > @endDate

		UPDATE  Periods
        SET     DateBegin = CASE WHEN DATEADD(quarter, 3, @startDate) > @endDate THEN @endDate ELSE dbo.AddQuarter(DATEADD(quarter, 2, @startDate)) END,
                DateEnd = CASE WHEN dbo.AddQuarter(DATEADD(quarter, 3, @startDate)) > @endDate THEN @endDate ELSE dbo.AddQuarter(DATEADD(quarter, 3, @startDate)) END
        WHERE   PeriodTypeId = 3 AND PeriodQuarter = 4 AND dbo.AddQuarter(DATEADD(quarter, 3, @startDate)) > @endDate
    END