Comparing a date range with another date range..
Posted: Thu Mar 15, 2012 10:08 pm
hi all,
I need some help from everyone. Basically I need to create a function where it can compute the record based on a period given, and also based on the rules(percentage) given. However, the rules(percentage) itself have an expired date, which means after certain period it will be expired. Also, the rules can be overwritten if its date overlapping by new rules. See the below example:
Database as below:
[text]CREATE TABLE rules (
id INTEGER PRIMARY KEY,
rules VARCHAR(50) NOT NULL,
date_start datetime NOT NULL,
date_end datetime NOT NULL,
remarks VARCHAR(50) NULL,
);[/text]
Example:
[text][rules] tbl record
---------------------
id, rules, date_start, date_end, remarks
1, 50%, 2011-12-23 00:00:00, 2012-1-23 00:00:00, " "
2, 40%, 2012-1-23 00:00:00, 2012-4-23 00:00:00, " "
3, 70%, 2012-4-23 00:00:00, 2012-9-23 00:00:00, " "
4, 30%, 2012-9-23 00:00:00, 2012-12-23 00:00:00, " "
5, 50%, 2012-6-23 00:00:00, 2012-12-23 00:00:00, " "[/text]
See, rules 1 start from 2011 December 23 and will expired on 2012 Jan 23. Same applies to the rest. But rules 5 starts from 2012 June and expired on 2012 December 23. If the user select the date start on 1 April 2012 and date ends on 1 December 2012, what rules applicable to them? Answer should be system will select 40% for 1 April 2012 to 23 April 2012, and 70% for 24 April 2012 to 23 June 2012, and 50% 24 June 2012 to 1 December 2012.
There's a form where user can select a date range and click [generate]. Basically the form looks like the below:
[text]
Date Start: [Jquery date picker here ]
Date End: [Jquery date picker here ]
[Generate Report Button]
[/text]
The output for the example should be:
[text]
40% (1 April 2012 to 23 April 2012)
70% (24 April 2012 to 23 June 2012)
50% (24 June 2012 to 1 December 2012)
[/text]
I've cracked my brain but have no idea how to code this out... someone can help me on this?
I need some help from everyone. Basically I need to create a function where it can compute the record based on a period given, and also based on the rules(percentage) given. However, the rules(percentage) itself have an expired date, which means after certain period it will be expired. Also, the rules can be overwritten if its date overlapping by new rules. See the below example:
Database as below:
[text]CREATE TABLE rules (
id INTEGER PRIMARY KEY,
rules VARCHAR(50) NOT NULL,
date_start datetime NOT NULL,
date_end datetime NOT NULL,
remarks VARCHAR(50) NULL,
);[/text]
Example:
[text][rules] tbl record
---------------------
id, rules, date_start, date_end, remarks
1, 50%, 2011-12-23 00:00:00, 2012-1-23 00:00:00, " "
2, 40%, 2012-1-23 00:00:00, 2012-4-23 00:00:00, " "
3, 70%, 2012-4-23 00:00:00, 2012-9-23 00:00:00, " "
4, 30%, 2012-9-23 00:00:00, 2012-12-23 00:00:00, " "
5, 50%, 2012-6-23 00:00:00, 2012-12-23 00:00:00, " "[/text]
See, rules 1 start from 2011 December 23 and will expired on 2012 Jan 23. Same applies to the rest. But rules 5 starts from 2012 June and expired on 2012 December 23. If the user select the date start on 1 April 2012 and date ends on 1 December 2012, what rules applicable to them? Answer should be system will select 40% for 1 April 2012 to 23 April 2012, and 70% for 24 April 2012 to 23 June 2012, and 50% 24 June 2012 to 1 December 2012.
There's a form where user can select a date range and click [generate]. Basically the form looks like the below:
[text]
Date Start: [Jquery date picker here ]
Date End: [Jquery date picker here ]
[Generate Report Button]
[/text]
The output for the example should be:
[text]
40% (1 April 2012 to 23 April 2012)
70% (24 April 2012 to 23 June 2012)
50% (24 June 2012 to 1 December 2012)
[/text]
I've cracked my brain but have no idea how to code this out... someone can help me on this?