kiran kiran - 3 months ago 11
SQL Question

Is that possible to write the below query using CTE recursion?

create table #temp (date date)
declare @X date
set @X = '2016-7-01'
declare @Y date
set @Y = cast (getdate() as date)
while(@X<=@Y)
begin
if (datename(WEEKDAY,@X) = 'Sunday')
insert into #temp values (@X)
set @X = cast(((cast(@X as datetime))+1)as date)
continue
end
select * from #temp
drop table #temp


Is that possible to write the above query using CTE recursion?

Answer

You can use a CTE to create a numbers table. You can then use the numbers table to get your dates like so:

Declare @Startdate Datetime = '2016-07-01'
Declare @EndDate Datetime = '2016-08-29'

;with
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N4)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
and datename(WEEKDAY, DATEADD(day,num-1,@startdate)) = 'Sunday'

Each table (N0 to nums) effectively multiplies the number of rows in the previous 'table', so you end up with 65,536 rows of numbers in nums (you can do less or more by adding or removing table NX as required). Then, use the numbers table to add days to your start date(SELECT DATEADD(day,num-1,@startdate) as thedate) , where the dates returned are in your date range, and the weekday is Sunday.

Also, because the numbers in nums start at 1, we use nums-1 in our select, so as to avoid skipping over the first date in our series, effectively giving us DATEADD(day, 0, @startdate) in our first row.