Complex querying - generating list in date range. (complex windowing, recursive querying)

T开发者_开发百科able:

I'm using MYSQL.

 tax_year_end_changes | CREATE TABLE `tax_year_end_changes` (  
  `id` int(11) NOT NULL auto_increment,
   `company_id` int(11) NOT NULL,
    `date` date NOT NULL default '1970-01-01',
    `reason` enum('Fiscal_Year_End','Control_Change') collate utf8_unicode_ci NOT
     NULL default 'Fiscal_Year_End',
     `created_at` datetime default NULL,
     `updated_at` datetime default NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `company_id` (`company_id`,`date`,`reason`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

This table contains a list of fiscal year ends for a company. When the company fiscal year end changes, the table just has the new fiscal year end. Default fiscal year ends are stored as from 1970. Changes are stored effect from the change date. Example data:

select company_id, date, reason from tax_year_end_changes;

1 1970-12-31 Fiscal_Year_End
1 2011-01-31 Fiscal_Year_End
3 1970-01-31 Fiscal_Year_End
4 1970-11-30 Fiscal_Year_End

Here, company 1 has fiscal year end of December 31 until 2010. In 2011, FYE changes to January 31. So for this company, the valid FYE are:

December 31, 2010 January 31, 2011 January 31, 2012 . . .

I want to generate a list of valid FYE given a date range. Specifically,

I'm given a starting date, and number of months.

I want to generate something like:

Use Case 1

Start Date: 2010-11-01
Number of Months: 3

company_id, month, tax_year_end_date

4 11 2010-11-30
1 12 2010-12-31
3 01 2011-01-31
1 01 2011-01-31

Use Case 2

Start Date: 2011-11-01
Number of Months: 3

company_id, month, tax_year_end_date

4 11 2011-11-30
3 01 2011-01-31
1 01 2011-01-31

Notes: Notice how in Use case 2, 2011-12-31 doesn't appear for company 1 as that year is no longer valid.