Recurring Date Calculator SQL

0
votes

I'm looking to create a recurring date calculator that will correspond to EVENTS on a specific calendar date. Each event has its own rule. EX. for EVENT A rule is "Occurs Tuesday monthly with the start date Tuesday 9-17-2019. im using a time dimension table with every date populated until 2025. Using these dates I was using in the WHERE clause 

WHERE dayname = 'tuesday' and ( DATEDIFF(DAY, '2019-09-17', Calendar_Date) % 28 ) = 0 to get monthly tuesdays.

But I'm running into issues when there are two 5 Tuesdays in a month. 3-3-2020 and 3-31-2020. Need date populated to be 4-7-2020 instead of 3-31-2020.

Can anyone help with a solution?

1
sqlsql-serverdate
Why are you trying to be clever with date arithmetic and modulo? You obviously have very clear logic to find all tuesdays - you just need to find the first tuesday of each month. That's a hint - how would you find that value for a specific month (i.e., where calendar_date >= '20190901' and calendar_date < '20191001')? And keep in mind that a calendar table might have easier ways to find any date within Sept 2019. - SMor

1 Answers

1
votes

With your query, you're pretty close. All you need to do is to then sub-select 

SELECT MIN(CalendarDate) AS CalendarDate
FROM (your query goes here) AS d
GROUP BY YEAR(CalendarDate), MONTH(CalendarDate);