INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Month End Date List

Month End Date List

(OP)
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

CODE

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

RE: Month End Date List

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

RE: Month End Date List

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

CODE

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...

CODE

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

RE: Month End Date List

(OP)
Dave

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close