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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
On line 2, change -12 to -11.

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

Good Luck,
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
ty
0
Aneesh RetnakaranDatabase AdministratorCommented:
What exactly are you looking this time, please post the expected results.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
PortletPaulEE Topic AdvisorCommented:
@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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.