Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Change a CTE to use table variable

Author  Topic 

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-04 : 19:22:20
Hi

I've found the following code which I need to change from a CTE to a table variable. I'm new to SQL, so any help is very much appreciated.


declare @DateFrom DateTime
declare @DateTo DateTime

set @DateFrom ='2013-04-29'
set @DateTo = '2013-05-27'


;WITH CTE(classDate)
AS
(
Select @DateFrom
Union All
Select DATEADD(d,1,classDate)FROM CTE
Where classDate<@DateTo
)

select classDate
from CTE
where DATENAME(dw,classDate)In('Monday')


Thanks as always

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-04 : 22:57:35
Here is a way:
[CODE]
declare @DateFrom Date
declare @DateTo Date

set @DateFrom ='2013-05-04'
set @DateTo = '2013-06-27'

CREATE TABLE #TMP(classDate Date)

INSERT INTO #TMP(classDate)
SELECT AllDates from
(Select DATEADD(d, number, @dateFrom) as AllDates from
master..spt_values where type = 'p' and number between 0 and
datediff(dd, @dateFrom, @dateTo)) AS D1
WHERE DATENAME(dw, D1.AllDates)In('Monday');

SELECT * FROM #TMP;

DROP TABLE #TMP;


[/CODE]
Go to Top of Page

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-05 : 05:17:43
Perfect. Thanks.

quote:
Originally posted by MuMu88

Here is a way:
[CODE]
declare @DateFrom Date
declare @DateTo Date

set @DateFrom ='2013-05-04'
set @DateTo = '2013-06-27'

CREATE TABLE #TMP(classDate Date)

INSERT INTO #TMP(classDate)
SELECT AllDates from
(Select DATEADD(d, number, @dateFrom) as AllDates from
master..spt_values where type = 'p' and number between 0 and
datediff(dd, @dateFrom, @dateTo)) AS D1
WHERE DATENAME(dw, D1.AllDates)In('Monday');

SELECT * FROM #TMP;

DROP TABLE #TMP;


[/CODE]

Go to Top of Page
   

- Advertisement -