Build help data for common table expression (CTE) recursion in SQL Server

The source of this article: http://www.cnblogs.com/wy123/p/5960825.html

When we do the development, sometimes we need some help data, must need continuous number, continuous interval of time, continuous quarterly date and so on.
Many people use the Master Library's spt_values system table, which of course is not a problem

such as the following (not finished, the result is 0-2047)

This can also be used, but the feeling is not flexible, one is not a random account can access the master database, but he has only such a continuous number in this,
If you want another result set, you don't get it.
Similar data can be generated with the recursive return of a common table expression CTE
For example, the 0-2047 result set above

; withGeneratehelpdata as(    Select 0  asIDUnion  All    SelectId+1  fromGeneratehelpdatawhereId<2047)SelectId fromGeneratehelpdataoption(maxrecursion2047);

Can be directly let the CTE parameter logical operation, can also generate temporary tables, to achieve the purpose of multiple reuse, so feel is not very refreshing?

1, generate consecutive numbers (of course, the starting value of the number, the interval value can be customized)

--generate consecutive numbers; withGeneratehelpdata as(    Select 0  asIDUnion  All    SelectId+1  fromGeneratehelpdatawhereId<2047)SelectId fromGeneratehelpdataoption(maxrecursion2047);

2,cte recursive generation of consecutive dates

--generate a continuous date; withGeneratehelpdata as(    Select cast('2016-10-01'  asDate as [Date]    Union  All    Select DATEADDD1,[Date]) fromGeneratehelpdatawhere [Date]<'2017-01-01')Select [Date]  fromGeneratehelpdata;

3, generate a continuous interval of point in time

Sometimes some stats need to be grouped by one-hour or half-hour intervals, such as counting hours of data that don't have half an hour in a day, etc.

--generate a point in time for successive intervals; withGeneratehelpdata as(    Select 1  asIdcast('00:00:00'  asTime0)) astimesectionUnion  All    SelectId+1  asIdcast(DateAdd(MI, -, timesection) asTime0)) astimesection fromGeneratehelpdatawhereId< the)Select *  fromGeneratehelpdata

Of course, there's a lot of flexibility here, and a little bit of distortion.

--more than a bit of distortion; withGeneratehelpdata as(    Select 1  asIdcast('00:00:00'  asTime0)) astimesectionUnion  All    SelectId+1  asIdcast(DateAdd(MI, -, timesection) asTime0)) astimesection fromGeneratehelpdatawhereId< the)Selecta.timesection astimesectionfrom,b.timesection asTimesectionto,cast(a.timesection as varchar(Ten))+'~'+cast(b.timesection as varchar(Ten)) astimesection fromGeneratehelpdata AInner JoinGeneratehelpdata B ona.ID=b.ID-1

4, generate the last day of the consecutive quarter

DECLARE @begin_dateDate= '2014-12-31',@end_dateDate= '2016-12-31'; withGeneratehelpdata as(    Select         CAST( Case                      when  Right(@begin_date,5)='12-30'                  Then DATEADD( Day,1,@begin_date)                     ELSE @begin_date                 END  asDATE) asEndingDateUNION  All    SELECT              Case  when  Right(DATEADD(QQ,1, EndingDate),5)='12-30'          Then  DATEADD( Day,1,DATEADD(QQ,1, EndingDate)) ELSE DATEADD(QQ,1, EndingDate)END  asEndingDate fromGeneratehelpdatawhereEndingDate< @end_date)Select *  fromGeneratehelpdata

Transform to generate data between two date intervals

DECLARE @begin_dateDate= '2014-12-31',@end_dateDate= '2016-12-31'; withGeneratehelpdata as(    Select 1  asID,CAST( Case                      when  Right(@begin_date,5)='12-30'                  Then DATEADD( Day,1,@begin_date)                     ELSE @begin_date                 END  asDATE) asEndingDateUNION  All    SELECTId+1  asID, Case  when  Right(DATEADD(QQ,1, EndingDate),5)='12-30'          Then  DATEADD( Day,1,DATEADD(QQ,1, EndingDate)) ELSE DATEADD(QQ,1, EndingDate)END  asEndingDate fromGeneratehelpdatawhereEndingDate< @end_date)Selecta.endingdate asdatefrom,b.endingdate asDateto,cast(a.endingdate as varchar(Ten))+'~'+cast(b.endingdate as varchar(Ten)) astimesection fromGeneratehelpdata AInner JoinGeneratehelpdata B ona.ID=b.ID-1

It is important to note that the default number of occurrences of the CTE recursion is 100, and if you do not specify a recursive number (option (Maxrecursion N);), the default maximum recursion count is exceeded and an error is returned.

Summary: This article demonstrates several common cases of generating help data based on CTE recursion, which can be flexibly handled according to the recursive characteristics of the CTE, if need help data.

Build help data for common table expression (CTE) recursion in SQL Server