Month End Date List 1

[IanWaterman]

I need to get Month End Dates in a flexible date range. These dates are then converted to DateIDs for use in my main query.
Code below works fine as standalone, but I f I try to convert to another CTE or use as a Subquery it get an error associated with OPTION.

Is there a way around this, or a better solution to get my list of month end DatyeIDs

Declare @Start datetime 
Declare @End datetime 
Select @End = Cast(getdate() as Date) 
Select @Start = Dateadd(m,-6, @End) 
; 
With CTE as 
( Select @Start as MDate
,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last] 
UNION ALL 
Select MDate+1
,Case When DatePart(mm,MDate+1)<>DatePart(mm,MDate+2) then 1 else 0 end 
from CTE 
Where MDate<@End ) 

Select DP.ID MonthEndID 
from CTE 
inner Join D_DatePeriod DP 
on MDate = DP.ActualDate 
where [Last]=1 OPTION ( MAXRECURSION 0 )]

Thank you
Ian

 

[gmmastros]

Can you show some sample data from the D_DatePeriod table and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

Could you just pull the month-end dates directly from D_DatePeriod?

SELECT ID MonthEndID
  FROM D_DatePeriod
 WHERE ActualDate BETWEEN @Start AND @End
   AND DATEPART(MONTH, ActualDate) <> DATEPART(MONTH, DATEADD(DAY, 1, ActualDate))

EDIT: This may be easier to read...

SELECT ID MonthEndID
  FROM D_DatePeriod
 WHERE ActualDate BETWEEN @Start AND @End
[COLOR=#EF2929]   AND MONTH(ActualDate) <> MONTH(DATEADD(DAY, 1, ActualDate))[/color]

 

[IanWaterman]

Dave

Excellent bit of lateral thinking, I was obviously making it far too complicated. Your solution worked perfectly.
Thank you
Ian