Monday, July 26, 2010

Identifying nth Weekday of a month in reports

Requirement: Identify the 2nd Thursday of the current month.

Solution:

Create data items ToDate, 1stDateofMonth, 1stDayofMonth, Nth, Day , AddDays, NthDay

ToDate - current_date
 
1stDateofMonth - _first_of_month(ToDate)
 
1stDayofMonth - _day_of_week(1stDateofMonth ,1) // Assuming Monday is Day 1
 
Nth - 2 // The nth value of the weekday required, in our case we require 2nd Thursday
 
Day - 4 // Assuming Monday is Day 1, then Thursday is Day 4
 
AddDays - case when [Day] >= [1stDayofMonth]
then [Day] - [1stDayofMonth]
else
([Day] + [1stDayofMonth]) -1
end
 
NthDay - _add_days([1stDateofMonth],(([Nth]-1) * 7 ) + [AddDay])
 
NthDay returns the 2nd Thursday of the month.
 

No comments: