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?
Comparing a date range with another date range..
Moderator: General Moderators
Re: Comparing a date range with another date range..
Unless you absolutely need a full timestamp, I would be using date only for the entered data in the format of 'Y-m-d'.
Query I think might look like
I don't think this works too well if your storing your date in the format of 'Y-m-d H:i:s'
Then make sure to grab all matches through PHP
Off the top of my head at least, and its Friday, so forgive me if I've made a minor mistake 
Query I think might look like
Code: Select all
SELECT * FROM `rules` WHERE `date_start` => '<startdate>' AND `date_end` <= '<enddate>'Then make sure to grab all matches through PHP
Code: Select all
$result = mysql_query("SELECT * FROM `rules` WHERE `date_start` => '2012-04-01' AND `date_end` <= '2012-12-01'");
while($row = mysql_fetch_assoc($result)){
/* Formatting is just for legibility */
print $row['rules']." (".
date('j F Y', strtotime($row['start_date'])).
" to ".
date('j F Y', strtotime($row['end_date']))).
"<br/>";
}Re: Comparing a date range with another date range..
Hi Weirdy,
Thanks for your quick reply and I understand that today is Friday so maybe you overlook my problems up there, haha...
Your query will yield the following result but is not the right answer for me..
[text]
40%, 2012-1-23 00:00:00 to 2012-4-23 00:00:00
70%, 2012-4-23 00:00:00 to 2012-9-23 00:00:00
30%, 2012-9-23 00:00:00 to 2012-12-23 00:00:00
50%, 2012-6-23 00:00:00 to 2012-12-23 00:00:00
[/text]
Problem statements:
User input:
[text]starts on 1 April 2012 and date ends on 1 December 2012[/text]
But I'm stuck in how to get the below...
System will return:
[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]
Thanks for your quick reply and I understand that today is Friday so maybe you overlook my problems up there, haha...
Code: Select all
$result = mysql_query("SELECT * FROM `rules` WHERE `date_start` => '2012-04-01' AND `date_end` <= '2012-12-01'");
while($row = mysql_fetch_assoc($result)){
/* Formatting is just for legibility */
print $row['rules']." (".
date('j F Y', strtotime($row['start_date'])).
" to ".
date('j F Y', strtotime($row['end_date']))).
"<br/>";
}
[text]
40%, 2012-1-23 00:00:00 to 2012-4-23 00:00:00
70%, 2012-4-23 00:00:00 to 2012-9-23 00:00:00
30%, 2012-9-23 00:00:00 to 2012-12-23 00:00:00
50%, 2012-6-23 00:00:00 to 2012-12-23 00:00:00
[/text]
Problem statements:
User input:
[text]starts on 1 April 2012 and date ends on 1 December 2012[/text]
But I'm stuck in how to get the below...
System will return:
[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]
Re: Comparing a date range with another date range..
Well the only way i can see in order to do what your after without some other way to identify the particular records you want, will be to loop through looking to see if their are any ranges inside the selected date range for any dates which are subsets of others and ignoring them, giving you only the larger date periods.
ie. 2012-9-23 -- 2012-12-23 would be a subset of 2012-6-23 -- 2012-12-23
So you would really have to grab the entire array, then add a loop to remove any values which are >= date_start && <= date_end
ie. 2012-9-23 -- 2012-12-23 would be a subset of 2012-6-23 -- 2012-12-23
So you would really have to grab the entire array, then add a loop to remove any values which are >= date_start && <= date_end
Re: Comparing a date range with another date range..
Hi Weiry,
Thanks for your reply again. Yes, I need to do a looping there, but my record isn't only a few, it's quite a number of records I need to compute and filter them according to the date range and rules being applied to them. I think my method will definitely slow down the process, any cleaner or smarter way of doing so without looping it? Or that is the only way to solve the problems...?
Thanks for your reply again. Yes, I need to do a looping there, but my record isn't only a few, it's quite a number of records I need to compute and filter them according to the date range and rules being applied to them. I think my method will definitely slow down the process, any cleaner or smarter way of doing so without looping it? Or that is the only way to solve the problems...?