Comparing a date range with another date range..

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Comparing a date range with another date range..

Post by phpwalker »

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?
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Comparing a date range with another date range..

Post by Weiry »

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

Code: Select all

SELECT * FROM `rules` WHERE `date_start` => '<startdate>' AND `date_end` <= '<enddate>'
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

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/>";

}
Off the top of my head at least, and its Friday, so forgive me if I've made a minor mistake :)
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: Comparing a date range with another date range..

Post by phpwalker »

Hi Weirdy,

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/>";
}
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]
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Comparing a date range with another date range..

Post by Weiry »

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
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: Comparing a date range with another date range..

Post by phpwalker »

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...?
Post Reply