Complicated Date/Job Frequency calculation problem.

[moonman89]

Hey guys,

Hoping someone can help me with the logic for this one. I can't figure out how to do it.

I've got a table of jobs, with a field for start date (Or once off date) and a field for job frequency, IE. 14 days, 28 days 42 days ETC...

 

I need to select all jobs for a particular date EG, 1/7/09 ( I choose that month cause it has the same amount of days as the next )

 

If there is a job on 1/6/09 with a freq of 31 days, it needs to appear on my list.

But on the same note, if there is a job on 30/6/09 with a freq of 2 days, that needs to appear too.

Same as if there is a job on 12/06/09 with a freq of 19 days.

 

How would you go about selecting these jobs?

There has to be a way to do this...

 

The database is MySql.

[.josh]

If I understood you correctly...

 

// example array of data. build from db query
$jobs[] = array('startDate' => "2009-01-01", 'frequency' => 2);
$jobs[] = array('startDate' => "2009-03-25", 'frequency' => 30);
$jobs[] = array('startDate' => "2009-06-14", 'frequency' => 12);
$jobs[] = array('startDate' => "2009-07-14", 'frequency' => 14);
$jobs[] = array('startDate' => "2009-07-25", 'frequency' => 3);

// target date
$tdate = "2009-07-28";
$date = strtotime($tdate);

// foreach job...
foreach($jobs as $job) {
  // get the timestamp for startDate + frequency days in future
  $jsD = strtotime($job['startDate']);
  $cDate  = mktime(0, 0, 0, date("m",$jsD),   date("d",$jsD)+$job['frequency'],   date("Y",$jsD));

  // if dates match up...
  if ($cDate == $date)
  // add to list
    $targetDates[] = $job['startDate'];
} // end foreach

// dump out data and results
echo "<pre>";
print_r($jobs);
echo "target date: " . $tdate . "<br/>"; 
print_r($targetDates); 

 

output:

Array
(
    [0] => Array
        (
            [startDate] => 2009-01-01
            [frequency] => 2
        )

    [1] => Array
        (
            [startDate] => 2009-03-25
            [frequency] => 30
        )

    [2] => Array
        (
            [startDate] => 2009-06-14
            [frequency] => 12
        )

    [3] => Array
        (
            [startDate] => 2009-07-14
            [frequency] => 14
        )

    [4] => Array
        (
            [startDate] => 2009-07-25
            [frequency] => 3
        )

)
target date: 2009-07-28
Array
(
    [0] => 2009-07-14
    [1] => 2009-07-25
)

[sasa]

SELECT * FROM `dates` WHERE DATEDIFF('2009-07-01', start_date) BETWEEN 0 AND frequency

[.josh]

hmm...are you sure that query is right? thinking maybe it should be

 

SELECT * FROM `dates` WHERE DATEDIFF('2009-07-01', start_date) = frequency

[moonman89]

If I understood you correctly...

 

// example array of data. build from db query
$jobs[] = array('startDate' => "2009-01-01", 'frequency' => 2);
$jobs[] = array('startDate' => "2009-03-25", 'frequency' => 30);
$jobs[] = array('startDate' => "2009-06-14", 'frequency' => 12);
$jobs[] = array('startDate' => "2009-07-14", 'frequency' => 14);
$jobs[] = array('startDate' => "2009-07-25", 'frequency' => 3);

// target date
$tdate = "2009-07-28";
$date = strtotime($tdate);

// foreach job...
foreach($jobs as $job) {
  // get the timestamp for startDate + frequency days in future
  $jsD = strtotime($job['startDate']);
  $cDate  = mktime(0, 0, 0, date("m",$jsD),   date("d",$jsD)+$job['frequency'],   date("Y",$jsD));

  // if dates match up...
  if ($cDate == $date)
  // add to list
    $targetDates[] = $job['startDate'];
} // end foreach

// dump out data and results
echo "<pre>";
print_r($jobs);
echo "target date: " . $tdate . "<br/>"; 
print_r($targetDates); 

 

output:

Array
(
    [0] => Array
        (
            [startDate] => 2009-01-01
            [frequency] => 2
        )

    [1] => Array
        (
            [startDate] => 2009-03-25
            [frequency] => 30
        )

    [2] => Array
        (
            [startDate] => 2009-06-14
            [frequency] => 12
        )

    [3] => Array
        (
            [startDate] => 2009-07-14
            [frequency] => 14
        )

    [4] => Array
        (
            [startDate] => 2009-07-25
            [frequency] => 3
        )

)
target date: 2009-07-28
Array
(
    [0] => 2009-07-14
    [1] => 2009-07-25
)

 

 

OK, that has done 90% of what I want, but I think that is only catching the first frequency I believe -- correct me if I'm wrong??

I need to add another loop for each job that keeps going until start date + frequency is past the requested date.

 

Does that sound right?

I need to catch EVERY occurrence, weather it the first or the 1111th.

 

 

[.josh]

I understood your OP to mean you have a target date, and you have a list of all these other dates with a number "frequency" and you wanted to know which date(s)+frequency = target date.

 

I'm not really sure what you're asking in this last post though.

[ignace]

I understood your OP to mean you have a target date, and you have a list of all these other dates with a number "frequency" and you wanted to know which date(s)+frequency = target date.

 

I'm not really sure what you're asking in this last post though.

 

What is 'OP'? Everyone is using it and I have no idea about what you all are talking about. Did I miss a memo?

[.josh]

OP = Original Post or Original Poster, depending on the context.

[.josh]

moonman:

 

I'm kind of sort of getting a vague idea maybe you are trying to figure out a "next available date" sort of thing, based off of a start date, frequency and target date.  So for instance, if you have the following info:

 

  start date    frequency

A 2009-06-01  30

B 2009-06-15  10

C 2009-07-09  2

 

target date

2009-07-28

 

You are wanting...

 

Next available date:

A 2009-07-31

B 2009-08-04

C 2009-07-31

 

is that correct?

 

 

[ignace]

I'm kind of sort of getting a vague idea maybe you are trying to figure out a "next available date" sort of thing, based off of a start date, frequency and target date.

 

I think he is trying to build an advertising kind of thing for jobs. How higher the frequency (and the money) the more it is shown, would be my best bet

[moonman89]

I ended up finding a much simpler way of doing it in mysql.

 

To answer your question though.

It is for a mowing franchise, but the person who wrote the program that handles all the job scheduling did the tables roughly like so:

 

Schedule_Date: 28-1-2009

Freq: 14

 

So what I'm after is a selection of all jobs for say 28-1-2009, I should get any jobs listed on the 28th that don't have a freq. And I should also get any jobs listed on either the 14th, or the December 31st that have a freq of 14 back until schedule_date or the job is cancelled.