homepage Welcome to WebmasterWorld Guest from 54.167.174.90
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MYSQL - Complex query involving date range
theanvah



 
Msg#: 4306527 posted 3:06 pm on May 1, 2011 (gmt 0)

I'm trying to find valid fiscal year rangers for a company.

Table:

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.

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved