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

/****** Object:  StoredProcedure [dbo].[AS_BigDimDate_AllYear]    Script Date: 2017/4/28 15:27:44 ******/-- 1. 创建表BigDimDateIF  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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BigDimDate]') AND type in (N'U'))BEGINCREATE 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]ENDGOSET ANSI_PADDING OFFGO/****** Object:  StoredProcedure [dbo].[AS_BigDimDate_AllYear]    Script Date: 2017/4/28 15:27:44 ******/-- 2.存储过程SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AS_BigDimDate_AllYear]') AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[AS_BigDimDate_AllYear] AS' ENDGO-- =============================================-- Author:<zhangge>-- Create date: <2014-12-11>-- Description:<日期纬度>-- =============================================ALTER PROCEDURE [dbo].[AS_BigDimDate_AllYear] ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    DECLARE @StartDate DATETIMEDECLARE @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))))beginSET @StartDate = @StartDate + 1continue endINSERT 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 1end --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 + 1ENDENDGO/*说明：1.首先创建表2.创建存储过程3.存储过程中  可以指定生成的日期范围*/