3rd <day_of_week> of the Month - MySQL

Question

I'm working on a recurrence application for events. I have a date range of say, January 1 2010 to December 31 2011. I want to return all of the 3rd Thursdays (arbitrary) of the each month, efficiently. I could do this pretty trivially in code, the caveat is that it must be done in a stored procedure. Ultimately I'd want something like:

CALL return_dates(event_id);

That event_id has a start_date of 1/1/2010 and end_date of 12/31/2011. Result set would be something like:

1/20/2010
2/14/2010
3/17/2010
4/16/2010
5/18/2010
etc. 

I'm just curious what the most efficient method of doing this would be, considering I might end up with a very large result set in my actual usage.

Answer 1

One idea that comes to mind - you can create a table and store the dates you're interested in there.

Answer 2

Ok, I haven't tested it, but I think the most efficient way of doing it is via a tally table which is a useful thing to have in the db anyway:

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[num_seq]') AND type in (N'U'))
DROP TABLE [dbo].[num_seq];

SELECT TOP 100000 IDENTITY(int,1,1) AS n
INTO num_seq
FROM MASTER..spt_values a, MASTER..spt_values b;

CREATE UNIQUE CLUSTERED INDEX idx_1 ON num_seq(n);

You can then use this to build up the date range between the two dates. It's fast because it just uses the index (in fact often faster than a loop, so I'm led to believe)

create procedure getDates
    @eventId int
AS
begin

declare @startdate datetime
declare @enddate datetime

--- get the start and end date, plus the start of the month with the start date in
select @startdate=startdate, 
       @enddate=enddate
       from events where eventId=@eventId

  select
         @startdate+n AS date,
       from
         dbo.num_seq tally
       where
        tally.n<datediff(@monthstart, @enddate) and
        Datepart(dd,@startdate+n) between 15 and 21 and
        Datepart(dw, @startdate+n) = '<day>'

Aside from getting the start and end dates, the third x id each month must be between the 15th and the 21st inclusive. The day names in that range must be unique, so we can locate it straight away.

If you wanted the second dayname, just modify the range appropriately or use a parameter to calculate it.

It constucts a date table using the startdate, and then adding days on (via the list of numbers in the tally table) until it reaches the end date.

Hope it helps!