kiran - 1 month ago 5x
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?

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)
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.