sql 生成日期序列及相关关系

/****** Object:  StoredProcedure [dbo].[AS_BigDimDate_AllYear]    Script Date: 2017/4/28 15:27:44 ******/

-- 1. 创建表BigDimDate

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AS_BigDimDate_AllYear]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AS_BigDimDate_AllYear]
GO
/****** Object:  Table [dbo].[BigDimDate]    Script Date: 2017/4/28 15:27:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BigDimDate]') AND type in (N'U'))
DROP TABLE [dbo].[BigDimDate]
GO
/****** Object:  Table [dbo].[BigDimDate]    Script Date: 2017/4/28 15:27:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BigDimDate]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BigDimDate](
	[DateKey] [int] NOT NULL,
	[FullDateAlternateKey] [datetime] NOT NULL,
	[DayNumberOfWeek] [tinyint] NOT NULL,
	[ChineseDayNameOfWeek] [nvarchar](10) NOT NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NOT NULL,
	[DayNumberOfMonth] [tinyint] NOT NULL,
	[DayNumberOfYear] [smallint] NOT NULL,
	[WeekNumberOfYear] [tinyint] NOT NULL,
	[ChineseMonthName] [nvarchar](10) NOT NULL,
	[EnglishMonthName] [nvarchar](10) NOT NULL,
	[SpanishMonthName] [nvarchar](10) NOT NULL,
	[FrenchMonthName] [nvarchar](10) NOT NULL,
	[MonthNumberOfYear] [tinyint] NOT NULL,
	[CalendarQuarter] [tinyint] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarSemester] [tinyint] NOT NULL,
	[WeekMonth] [int] NULL,
	[WeekNumberOfMonth] [int] NULL,
	[RangeOfWeek] [nchar](11) NULL,
	[MonthKey] [int] NULL,
	[CalendarHalfYear] [int] NULL,
	[CalendarHalfYearName] [varchar](50) NULL,
	[CalendarHalfYearDESC] [varchar](50) NULL,
	[CalendarHalfYearEndMonth] [int] NULL,
	[CalendarQuarterName] [varchar](50) NULL,
	[DateType] [int] NOT NULL,
	[DateTypeName] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO






/****** Object:  StoredProcedure [dbo].[AS_BigDimDate_AllYear]    Script Date: 2017/4/28 15:27:44 ******/

-- 2.存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AS_BigDimDate_AllYear]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[AS_BigDimDate_AllYear] AS' 
END
GO
-- =============================================
-- Author:		<zhangge>
-- Create date: <2014-12-11>
-- Description:	<日期纬度>
-- =============================================
ALTER PROCEDURE [dbo].[AS_BigDimDate_AllYear] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @StartDate DATETIME
	DECLARE @EndDate DATETIME

    --设置日期纬度范围
    SET @StartDate= '2000-01-01'
    SET @EndDate= '2015-12-31'
--select * into TestBigDimDate from LH_B3.dbo.BigDimDate where 1=2
   

	--SELECT @StartDate = '2015-01-01',
	--	   @EndDate = '2015-12-31'
	--select DATEPART(WEEKDAY, '2009-01-01 00:00:00.000'),DATEPART(WEEK,'2009-01-03 00:00:00.000')
	WHILE (@StartDate <= @EndDate)
	BEGIN
		--检测数据存在则越过继续循环
		if(exists(select * from BigDimDate where DateKey= (CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT))))
		begin
			SET @StartDate = @StartDate + 1
			continue 
		end
		
		INSERT INTO BigDimDate 
		(		[DateKey]
			   ,[FullDateAlternateKey]
			   ,[DayNumberOfWeek]
			   ,[ChineseDayNameOfWeek]
			   ,[EnglishDayNameOfWeek]
			   ,[DayNumberOfMonth]
			   ,[DayNumberOfYear]
			   ,[WeekNumberOfYear]
			   ,[ChineseMonthName]
			   ,[EnglishMonthName]
			   ,[SpanishMonthName]
			   ,[FrenchMonthName]
			   ,[MonthNumberOfYear]
			   ,[CalendarQuarter]
			   ,[CalendarYear]
			   ,[CalendarSemester]
			   ,[WeekMonth]
			   ,[WeekNumberOfMonth]
			   ,[RangeOfWeek]
			   ,[MonthKey]
			   ,[CalendarHalfYear]
			   ,[CalendarHalfYearName]
			   ,[CalendarHalfYearDESC]
			   ,[CalendarHalfYearEndMonth]
			   ,[CalendarQuarterName]
			   ,[DateType]
			   ,[DateTypeName])
		 VALUES
			   (CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT)
			   ,convert(Datetime,CONVERT(varchar(100), @StartDate, 23))
			   ,case when DATEPART(WEEKDAY, @StartDate)=1 then 7 
					 else DATEPART(WEEKDAY, @StartDate)-1 
				end
			   ,case when DATEPART(WEEKDAY, @StartDate)=1 then '星期日' 
					 when DATEPART(WEEKDAY, @StartDate)=2 then '星期一' 
					 when DATEPART(WEEKDAY, @StartDate)=3 then '星期二' 
					 when DATEPART(WEEKDAY, @StartDate)=4 then '星期三' 
					 when DATEPART(WEEKDAY, @StartDate)=5 then '星期四' 
					 when DATEPART(WEEKDAY, @StartDate)=6 then '星期五' 
					 else '星期六'  
				end
			   ,case when DATEPART(WEEKDAY, @StartDate)=2 then 'Monday' 
					 when DATEPART(WEEKDAY, @StartDate)=3 then 'Tuesday' 
					 when DATEPART(WEEKDAY, @StartDate)=4 then 'Wednesday' 
					 when DATEPART(WEEKDAY, @StartDate)=5 then 'Thursday' 
					 when DATEPART(WEEKDAY, @StartDate)=6 then 'Friday' 
					 when DATEPART(WEEKDAY, @StartDate)=7 then 'Saturday' 
					 else 'Sunday'  
				end
			   ,DATEPART(DAY, @StartDate)
			   ,DATEPART(DAYOFYEAR, @StartDate)
			   ,case when DATEPART(WEEK, @StartDate)>1 and DATEPART(WEEKDAY, @StartDate)=1 then DATEPART(WEEK, @StartDate)-1
					 else DATEPART(WEEK, @StartDate)  
				end
			   ,case when DATEPART(MONTH, @StartDate)=1 then '一月' 
					 when DATEPART(MONTH, @StartDate)=2 then '二月' 
					 when DATEPART(MONTH, @StartDate)=3 then '三月' 
					 when DATEPART(MONTH, @StartDate)=4 then '四月' 
					 when DATEPART(MONTH, @StartDate)=5 then '五月' 
					 when DATEPART(MONTH, @StartDate)=6 then '六月' 
					 when DATEPART(MONTH, @StartDate)=7 then '七月' 
					 when DATEPART(MONTH, @StartDate)=8 then '八月' 
					 when DATEPART(MONTH, @StartDate)=9 then '九月' 
					 when DATEPART(MONTH, @StartDate)=10 then '十月' 
					 when DATEPART(MONTH, @StartDate)=11 then '十一月' 
					 else '十二月'  
				end
			   ,case when DATEPART(MONTH, @StartDate)=1 then 'January' 
					 when DATEPART(MONTH, @StartDate)=2 then 'February' 
					 when DATEPART(MONTH, @StartDate)=3 then 'March' 
					 when DATEPART(MONTH, @StartDate)=4 then 'April' 
					 when DATEPART(MONTH, @StartDate)=5 then 'May' 
					 when DATEPART(MONTH, @StartDate)=6 then 'June' 
					 when DATEPART(MONTH, @StartDate)=7 then 'July' 
					 when DATEPART(MONTH, @StartDate)=8 then 'August' 
					 when DATEPART(MONTH, @StartDate)=9 then 'September' 
					 when DATEPART(MONTH, @StartDate)=10 then 'October' 
					 when DATEPART(MONTH, @StartDate)=11 then 'November' 
					 else 'December'  
				end
			   ,case when DATEPART(MONTH, @StartDate)=1 then 'Enero' 
					 when DATEPART(MONTH, @StartDate)=2 then 'Febrero' 
					 when DATEPART(MONTH, @StartDate)=3 then 'Marzo' 
					 when DATEPART(MONTH, @StartDate)=4 then 'Abril' 
					 when DATEPART(MONTH, @StartDate)=5 then 'Mayo' 
					 when DATEPART(MONTH, @StartDate)=6 then 'Junio' 
					 when DATEPART(MONTH, @StartDate)=7 then 'Julio' 
					 when DATEPART(MONTH, @StartDate)=8 then 'Agosto' 
					 when DATEPART(MONTH, @StartDate)=9 then 'Septiembre' 
					 when DATEPART(MONTH, @StartDate)=10 then 'Octubre' 
					 when DATEPART(MONTH, @StartDate)=11 then 'Noviembre' 
					 else 'Diciembre'  
				end
			   ,case when DATEPART(MONTH, @StartDate)=1 then 'Janvier' 
					 when DATEPART(MONTH, @StartDate)=2 then 'Février' 
					 when DATEPART(MONTH, @StartDate)=3 then 'Mars' 
					 when DATEPART(MONTH, @StartDate)=4 then 'Avril' 
					 when DATEPART(MONTH, @StartDate)=5 then 'Mai' 
					 when DATEPART(MONTH, @StartDate)=6 then 'Juin' 
					 when DATEPART(MONTH, @StartDate)=7 then 'Juillet' 
					 when DATEPART(MONTH, @StartDate)=8 then 'Ao?t' 
					 when DATEPART(MONTH, @StartDate)=9 then 'Septembre' 
					 when DATEPART(MONTH, @StartDate)=10 then 'Octobre' 
					 when DATEPART(MONTH, @StartDate)=11 then 'Novembre' 
					 else 'Décembre'  
				end
			   ,DATEPART(MONTH, @StartDate)
			   ,DATEPART(QUARTER, @StartDate)
			   ,DATEPART(YEAR, @StartDate)
			   ,case when DATEPART(MONTH, @StartDate) between 1 and 6 then 1 
					 else 2 
				end
			   ,case when DATEPART(MONTH, DATEADD(DAY, 3, DATEADD(Day,1-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate)))=DATEPART(MONTH, @StartDate) then CONVERT(int,CONVERT(varchar(6), @StartDate, 112))
					 when DATEPART(MONTH, DATEADD(DAY, 3, DATEADD(Day,1-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate)))<>DATEPART(MONTH, @StartDate) then CONVERT(int,CONVERT(varchar(6), DATEADD(MONTH,-1, @StartDate), 112))
					 when DATEPART(MONTH, DATEADD(DAY, -2, DATEADD(Day,1-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate)))=DATEPART(MONTH, @StartDate) then CONVERT(int,CONVERT(varchar(6), @StartDate, 112))
					 when DATEPART(MONTH, DATEADD(DAY, -2, DATEADD(Day,1-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate)))<>DATEPART(MONTH, @StartDate) then CONVERT(int,CONVERT(varchar(6), DATEADD(MONTH,-1, @StartDate), 112))
					 else CONVERT(int,CONVERT(varchar(6), @StartDate, 112))
			   end
			   ,case when DATEPART(WEEKDAY, dateadd(month,1,@StartDate)-day(dateadd(month,1,@StartDate)))<5 and @StartDate between dateAdd(Day,-DATEPART(WEEKDAY, dateadd(month,1,@StartDate)-day(dateadd(month,1,@StartDate))) + 2,dateadd(month,1,@StartDate)-day(dateadd(month,1,@StartDate))) and dateadd(month,1,@StartDate)-day(dateadd(month,1,@StartDate)) THEN 1
					 when DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), ''))<=4 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) >DATEPART(DAY,@StartDate) and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) >1 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,DateAdd(Month,-1,@StartDate)), DateAdd(Month,-1,@StartDate))), ''))<=4 THEN DATEDIFF(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,Dateadd(MONTH,-1,@StartDate)), Dateadd(MONTH,-1,@StartDate))), '') ,@StartDate)/7 + 1 
					 when DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), ''))<=4 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) >DATEPART(DAY,@StartDate) and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) >1 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,DateAdd(Month,-1,@StartDate)), DateAdd(Month,-1,@StartDate))), ''))>4 THEN DATEDIFF(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,Dateadd(MONTH,-1,@StartDate)), Dateadd(MONTH,-1,@StartDate))), '') ,@StartDate)/7 + 2
					 when DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), ''))<=4 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) >DATEPART(DAY,@StartDate) and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) =1 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,DateAdd(Month,-1,@StartDate)), DateAdd(Month,-1,@StartDate))), ''))>4 THEN 2
					 when DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), ''))<=4 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) <=DATEPART(DAY, @StartDate)   THEN DATEDIFF(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), ''),@StartDate)/7 + 1
					 when DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), ''))>4 and DATEPART(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), '')) <=DATEPART(DAY,@StartDate) THEN DATEDIFF(DAY, DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DATEPART( DAY,@StartDate), @StartDate)), ''),@StartDate)/7 + 2
					 else 1
				end --WeekNumberOfMonth
			   , CONVERT(nvarchar(2), DATEPART(MONTH, DATEADD(Day,1-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate))) + '.' + CONVERT(nvarchar(2), DATEPART(DAY, DATEADD(Day,1-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate))) + '-' + CONVERT(nvarchar(2), DATEPART(MONTH, DATEADD(Day,7-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate))) + '.' + CONVERT(nvarchar(2), DATEPART(DAY, DATEADD(Day,7-(DATEPART(Weekday,@StartDate)+@@DATEFIRST-2)%7-1,@StartDate)))--<RangeOfWeek, nchar(11),>
			   ,CONVERT(int,CONVERT(varchar(6), @StartDate, 112))
			   ,case when DATEPART(MONTH, @StartDate) between 1 and 6 then convert(int, CONVERT(nvarchar(4), DATEPART(YEAR, @StartDate) )+'1')
					 else convert(int, CONVERT(nvarchar(4), DATEPART(YEAR, @StartDate) )+'2') 
				end
			   ,case when DATEPART(MONTH, @StartDate) between 1 and 6 then CONVERT(nvarchar(4), DATEPART(YEAR, @StartDate) )+'年上半年'
					 else CONVERT(nvarchar(4), DATEPART(YEAR, @StartDate) )+'年下半年' 
				end
			   ,case when DATEPART(MONTH, @StartDate) between 1 and 6 then '1-6月'
					 else '7-12月' 
				end
			   ,case when DATEPART(MONTH, @StartDate) between 1 and 6 then convert(int, CONVERT(nvarchar(4), DATEPART(YEAR, @StartDate) )+'06')
					 else convert(int, CONVERT(nvarchar(4), DATEPART(YEAR, @StartDate) )+'12')
				end
			   ,case when DATEPART(QUARTER, @StartDate)=1 then '第一季度' 
					 when DATEPART(QUARTER, @StartDate)=2 then '第二季度' 
					 when DATEPART(QUARTER, @StartDate)=3 then '第三季度' 
					 else '第四季度'  
				end
			   ,case when DATEPART(WEEKDAY, @StartDate)=1 or DATEPART(WEEKDAY, @StartDate)=7 then 1  --双休日
					 else 0 --工作日
				end --再增加一个DateTypeName
			   ,case when DATEPART(WEEKDAY, @StartDate)=1 or DATEPART(WEEKDAY, @StartDate)=7 then '周末'  --双休日
					 else '平日' --工作日
				end
			   )
	            
		SET @StartDate = @StartDate + 1
	END

END

GO


/*

说明：
1.首先创建表
2.创建存储过程
3.存储过程中  可以指定生成的日期范围

*/