sql recursive CTE

Why does this return 13 records?  I just want it to return the first 12.  I can I make that happen?

 WITH _DateList ([DATE])
 AS (SELECT      DATEADD(MONTH, -12, DATEADD(month, DATEDIFF(month, -1, GETDATE()), 1)) [DATE]
     UNION ALL
     SELECT DATEADD(MONTH, 1, [DATE])
     FROM _DateList
     WHERE [DATE] < CAST(GETDATE() as DATE)
    )

SELECT * FROM _DateList

Open in new window

vbnetcoderAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
On line 2, change -12 to -11.

You're computing (and returning) month 0 plus the other 12 months.

Good Luck,
Kent
0
 
vbnetcoderAuthor Commented:
ty
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
What exactly are you looking this time, please post the expected results.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark WillsTopic AdvisorCommented:
its a bit back to front.... And the answer given so far is wrong (sorry Kent)

dateadd will add the periods to your 'root' date which you have as day 1 - so straight away your period is wrong.

to get to the start of "this" month, use  dateadd(month,datediff(month,0,getdate()),0)

then because getdate() has an actual day component, you need to take that into account.... casting getdate() as date doesnt, it still retains the day.

So, you will still have errors - try your code (with the -11) as if it were the 1st May 2018
 WITH _DateList ([DATE])
 AS (SELECT      DATEADD(MONTH, -11, DATEADD(month, DATEDIFF(month, -1, '20180501'), 1)) [DATE]
     UNION ALL
     SELECT DATEADD(MONTH, 1, [DATE])
     FROM _DateList
     WHERE [DATE] < CAST('20180501' as DATE)
    )

SELECT * FROM _DateList
/* results
DATE
2017-07-02 00:00:00.000
2017-08-02 00:00:00.000
2017-09-02 00:00:00.000
2017-10-02 00:00:00.000
2017-11-02 00:00:00.000
2017-12-02 00:00:00.000
2018-01-02 00:00:00.000
2018-02-02 00:00:00.000
2018-03-02 00:00:00.000
2018-04-02 00:00:00.000
2018-05-02 00:00:00.000
*/

Open in new window


What you need to do is get the start period and subtract a month, until it is before another 11 months ago
; WITH _DateList
 AS (SELECT      dateadd(month,datediff(month,0,getdate()),0) [DATE]
     UNION ALL
     SELECT DATEADD(MONTH, -1, [DATE])
     FROM _DateList
     WHERE [DATE] >= dateadd(month,-11,getdate())
	)

SELECT * FROM _DateList

Open in new window


And as proof let's use the first of May 2018 again
; WITH _DateList
 AS (SELECT      dateadd(month,datediff(month,0,'20180501'),0) [DATE]
     UNION ALL
     SELECT DATEADD(MONTH, -1, [DATE])
     FROM _DateList
     WHERE [DATE] > dateadd(month,-11,'20180501')
	)

SELECT * FROM _DateList
/* results
DATE
2018-05-01 00:00:00.000
2018-04-01 00:00:00.000
2018-03-01 00:00:00.000
2018-02-01 00:00:00.000
2018-01-01 00:00:00.000
2017-12-01 00:00:00.000
2017-11-01 00:00:00.000
2017-10-01 00:00:00.000
2017-09-01 00:00:00.000
2017-08-01 00:00:00.000
2017-07-01 00:00:00.000
2017-06-01 00:00:00.000
*/

Open in new window

1
 
PortletPaulfreelancerCommented:
@mark
The question does not request that each date be the first (or last, or midpoint) of each month, it simply asked why 13 rows instead of 12.

So, for the question as asked, Kent's answer is correct.

You certainly have offered additional useful information, but it wasn't requested.
0
 
Mark WillsTopic AdvisorCommented:
@Paul,

And it wont give 12 months all the time either - so the question should be still active if we interpret "I just want it to return the first 12" as the error at hand.

Regrettably, the root cause of the error is not resolved by simply changing -12 to -11

It could also be reasonably argued that use a date range in subsequent selects is just as viable a solution....

Indeed, if the question was asked a couple of weeks ago (1st May) the "answer" would not be "change -12 to -11" because that change would yield 11 months - (maybe that's why the question is being asked now, because 12 months were happening at a point in time...)

While I am prepared to acquiesce about start of month / mid month (whatever) I cannot in good conscience leave the answer as is - it will provide many an adopter of that code a few problems.

It would be interesting to find out what period is expected from DATEADD(month, DATEDIFF(month, -1, GETDATE()), 1)  it could well be the start of next month (hence the month of -1).  And if the desired day is 2. While it is quite possible, it doesnt seem to be consistent with other questions asked by vbnetcoder.

Cheers,
Mark Wills
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.