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

水深无声 2022-06-17 14:38 230阅读 0赞

/****** 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.存储过程中  可以指定生成的日期范围
    
    */

![Center][]![Center 1][]![Center 2][]![Center 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][]

[Center]: https://img-blog.csdn.net/20170428154021736?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvc2luYXRfMTY5OTg5NDU=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20170428154042049?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvc2luYXRfMTY5OTg5NDU=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20170428154054865?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvc2luYXRfMTY5OTg5NDU=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: /images/20220617/ac0f0579ed5546f4b9df0f5d014a1681.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220617/ac062c2bb429497da3066d37255b3aa4.png