Change a CTE to use table variable

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]

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources