数据生成时间表

TRUNCATE TABLE DateNorm
go
DECLARE @DateJ INT
DECLARE @CurDate DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2004-1-1'
 ---------这里填写起始时间
SET @EndDate = '2025-1-1'
 ---------这里填写结束时间
 --SELECT DATEDIFF(day, @StartDate, @EndDate) into @DateJ
  
SET @CurDate = @StartDate 
WHILE @CurDate < @EndDate 
    BEGIN
        INSERT  INTO DateNorm
                ( TheDate ,
                  TheDay ,
                  Quarter ,
                  TendayOfMonth ,
                  HalfYear ,
                  WeekNorm 
                )
        VALUES  ( @CurDate ,
                  CASE WHEN DATEPART(weekday, @CurDate) = 1 THEN '星期日'
                       WHEN DATEPART(weekday, @CurDate) = 2 THEN '星期一'
                       WHEN DATEPART(weekday, @CurDate) = 3 THEN '星期二'
                       WHEN DATEPART(weekday, @CurDate) = 4 THEN '星期三'
                       WHEN DATEPART(weekday, @CurDate) = 5 THEN '星期四'
                       WHEN DATEPART(weekday, @CurDate) = 6 THEN '星期五'
                       WHEN DATEPART(weekday, @CurDate) = 7 THEN '星期六'
                  END ,
                  DATENAME(quarter, @CurDate) ,
                  CASE WHEN DATEPART(day, @CurDate) <= 10 THEN '上旬'
                       WHEN DATEPART(day, @CurDate) <= 20
                            AND DATEPART(day, @CurDate) > 10 THEN '中旬'
                       WHEN DATEPART(day, @CurDate) > 20 THEN '下旬'
                  END ,
                  CASE WHEN DATEPART(month, @CurDate) <= 6 THEN '上半年'
                       WHEN DATEPART(month, @CurDate) > 6 THEN '下半年'
                  END ,
                  NULL
                ) 
        SET @CurDate = DATEADD(day, 1, @CurDate)
    END 
    
  /*
		周初始化
  */

DECLARE @FirstDay DATETIME 
DECLARE @TheDayTh INT 
DECLARE @addDays INT
DECLARE @beginDate DATETIME
DECLARE @FinishDate DATETIME 
DECLARE @TheDay NVARCHAR(15)
DECLARE @TheDate DATETIME

DECLARE C CURSOR
FOR
    SELECT  TheDate ,
            TheDay
    FROM    DateNorm
OPEN C
FETCH NEXT FROM C INTO @TheDate, @TheDay
WHILE @@fetch_status = 0 
    BEGIN
        SET @FirstDay = DATEADD(mm, DATEDIFF(mm, 0, @TheDate), 0) 
        IF ( @TheDate = @FirstDay ) 
            BEGIN 
                SET @TheDayTh = 1
                IF ( @TheDay = '星期日' ) 
                    SET @addDays = 0
                ELSE 
                    IF ( @TheDay = '星期六' ) 
                        SET @addDays = 1 
                    ELSE 
                        IF ( @TheDay = '星期五' ) 
                            SET @addDays = 2
                        ELSE 
                            IF ( @TheDay = '星期四' ) 
                                SET @addDays = 3
                            ELSE 
                                IF ( @TheDay = '星期三' ) 
                                    SET @addDays = 4
                                ELSE 
                                    IF ( @TheDay = '星期二' ) 
                                        SET @addDays = 5
                                    ELSE 
                                        IF ( @TheDay = '星期一' ) 
                                            SET @addDays = 6
			 
                SET @beginDate = @TheDate
                SET @FinishDate = DATEADD(dd, @addDays, @beginDate) 
                                              
                UPDATE  DateNorm
                SET     WeekNorm = @TheDayTh
                WHERE   TheDate BETWEEN @beginDate AND @FinishDate
            END
        ELSE 
            BEGIN
                IF ( @TheDate > @FinishDate ) 
                    BEGIN
                        SET @TheDayTh = @TheDayTh + 1 
                        IF ( @TheDay = '星期日' ) 
                            SET @addDays = 0
                        ELSE 
                            IF ( @TheDay = '星期六' ) 
                                SET @addDays = 1 
                            ELSE 
                                IF ( @TheDay = '星期五' ) 
                                    SET @addDays = 2
                                ELSE 
                                    IF ( @TheDay = '星期四' ) 
                                        SET @addDays = 3
                                    ELSE 
                                        IF ( @TheDay = '星期三' ) 
                                            SET @addDays = 4
                                        ELSE 
                                            IF ( @TheDay = '星期二' ) 
                                                SET @addDays = 5
                                            ELSE 
                                                IF ( @TheDay = '星期一' ) 
                                                    SET @addDays = 6 
				
                        SET @beginDate = @TheDate
                        SET @FinishDate = DATEADD(dd, @addDays, @beginDate) 
                            
                        UPDATE  DateNorm
                        SET     WeekNorm = @TheDayTh
                        WHERE   TheDate BETWEEN @beginDate AND @FinishDate
                    END
            END 
            
        FETCH NEXT FROM C INTO @TheDate, @TheDay
    END
CLOSE C
DEALLOCATE C