kiran - 1 year ago 64

SQL Question

`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 Source

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.