-- select * from [dbo].[GenDates2]( 'd', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates2]( 'w', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates2]( 'm', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates2]( 'y', getdate()-1, getdate()-16384 ); create or alter function [dbo].[GenDates2]( @Increment char(1), @Start datetime2(3), @End datetime2(3) ) returns @Range table( [Date] datetime2(3), [Count] int ) as begin; set @Increment = lower( @Increment ); with cte( [Date], [Count] ) AS ( select @Start [Date], 0 [Count] union all select case when @Increment = 'd' THEN dateadd(day, 1, [Date]) when @Increment = 'w' THEN dateadd(week, 1, [Date]) when @Increment = 'm' THEN dateadd(month, 1, [Date]) when @Increment = 'y' THEN dateadd(year, 1, [Date]) end, case when @Increment = 'd' THEN datediff( day, @Start, dateadd(day, 1, [Date])) when @Increment = 'w' THEN datediff(week, @Start, dateadd(week, 1, [Date])) when @Increment = 'm' THEN datediff(month, @Start, dateadd(month, 1, [Date])) when @Increment = 'y' THEN datediff(year, @Start, dateadd(year, 1, [Date])) end [Count] from [cte] where [Date] <= case when @Increment = 'd' THEN dateadd(day, -1, @End) when @Increment = 'w' THEN dateadd(week, -1, @End) when @Increment = 'm' THEN dateadd(month, -1, @End) when @Increment = 'y' THEN dateadd(year, -1, @End) end ) insert into @Range( [Date], [Count] ) select [Date], [Count] from [cte] option (maxrecursion 32767); return; end;
Categories