SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: lidongbo-- Create date: <Create Date,,>-- Description: 生成日期表，为营业统计使用-- =============================================CREATE PROCEDURE CreateDateTable -- Add the parameters for the stored procedure here @p_startTime datetime, @p_endTime datetimeASBEGIN /************生成时间临时表********/create table #etTtime_TempTable ( [ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY, [日期列] DateTime,[门店编号列] varchar(50))declare @etKdd int,@etKmmStr nvarchar(13),@etKddStr nvarchar(13),@etKwwStr nvarchar(13),@etKqqStr nvarchar(13),@start_Time datetime,@end_Time datetime,@type_Time int,@mdbh varchar(50)--set @start_Time='2010-01-01'/*设置起始时间*/--set @end_Time='2100-12-31'/*设置终止时间*/set @start_Time=@p_startTime/*设置起始时间*/set @end_Time=@p_endTime/*设置终止时间*/set @type_Time=1 /*生成类型 1为天 2为周 3为月 4为季度 5为年*/set @mdbh='zlwy4000480006'WHILE @start_Time<@end_Time /**/beginset @etKdd=datepart(dd,@start_Time)set @etKddStr=cast(@etKdd as nvarchar(13))
set @etKdd=datepart(mm,@start_Time)set @etKmmStr=cast(@etKdd as nvarchar(13))
set @etKdd=datepart(ww,@start_Time)set @etKwwStr=cast(@etKdd as nvarchar(13))
set @etKdd=datepart(qq,@start_Time)set @etKqqStr=cast(@etKdd as nvarchar(13))
if @type_Time=1BeginINSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKmmStr+'-'+@etKddStr,@mdbh)SET @start_Time=DATEADD(dd,1,@start_Time)Endif @type_Time=2BeginINSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKwwStr,@mdbh)SET @start_Time=DATEADD(ww,1,@start_Time)Endif @type_Time=3BeginINSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKmmStr,@mdbh)SET @start_Time=DATEADD(mm,1,@start_Time)Endif @type_Time=4BeginINSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKqqStr,@mdbh)SET @start_Time=DATEADD(qq,1,@start_Time)Endif @type_Time=5BeginINSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13)),@mdbh)SET @start_Time=DATEADD(yy,1,@start_Time) /*循环开始时间加1 直到等于设置的终止时间*/End
ContinueEnd
/*查询临时表*/--Select * From #etTtime_TempTable--delete from datetableinsert into datetable select id, 日期列 from #etTtime_TempTable/*删除临时表*/Drop Table #etTtime_TempTableENDGO
转载于:https://www.cnblogs.com/goto/p/3326579.html
