[SOLVED] Complicated Date Query

[SupaMonkey]

Hi Guys,

Was hoping I could get some help with a query I am struggling with. Basically I have a table that stores recurring services which clients have.

 

[b]Table structure:[/b]
CREATE TABLE `recurring_invoice_products` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `product_id` int(10) unsigned default NULL,
  `sell_price` decimal(9,2) default NULL,
  `quantity` int(10) unsigned default NULL,
  `details` text,
  `username` varchar(64) default NULL,
  `client_id` int(11) default NULL,
  `recurrence` smallint(6) default NULL,
  `recurrence_type` tinyint(4) default NULL,
  `last_occurrence` date default NULL,
  `start` date default NULL,
  `end` date default NULL,
  PRIMARY KEY  (`id`)
);

 

Now for the key explanations:

- start : start date of the service

- end : end date of the service

- recurrence_type: type of recurrence, 1 = days, 2 = months, 3 = years

- recurrence: this is the value of how many units of recurrence_type will pass, eg: 15 recurrence with a recurrence_type of 1 is 15 days

- last_occurrence: last time that this service 'occurred' (ie: was charged)

 

I already have a PHP function which can find out if it time to charge the client again or not, but I was wondering if could take it one step further and not even have MySQL return unnecessary rows.

 

[b]PHP Function:[/b]
function next_occurrence($recurrence,$recurrence_type,$from_date)
{
if ($recurrence_type == 1)
	return date('Y-m-d',mktime(0,0,0,date('m',strtotime($from_date)),date('d',strtotime($from_date))+$recurrence,date('Y',strtotime($from_date))));
elseif ($recurrence_type == 2)
	return date('Y-m-d',mktime(0,0,0,date('m',strtotime($from_date))+$recurrence,date('d',strtotime($from_date)),date('Y',strtotime($from_date))));
elseif ($recurrence_type == 3)
	return date('Y-m-d',mktime(0,0,0,date('m',strtotime($from_date)),date('d',strtotime($from_date)),date('Y',strtotime($from_date))+$recurrence));
else
	return false;
}

 

eg code of above function:

if (date('Y-m-d') = next_occurrence($row_data['recurrence'],$row_data['recurrence_type'],$row_data['last_occurrence'])) charge_client;

 

You guys know of a way?

Dont know if something like this is possible?

[fenway]

You want to do what?

[SupaMonkey]

In a single query I basically would like to return all the rows in 'recurring_invoice_products' which their last_occurrence value + the value of recurrence (based on the recurrence type) = today.

 

For example, lets say I have two records in recurring_invoice_products:

+------------+------------+----------------+------------+-----------------+
| start      | end        | last_occurence | recurrence | recurrence_type |
+------------+------------+----------------+------------+-----------------+
| 2007-01-03 | 2999-01-01 | 2007-10-01     |          1 |               1 |
| 2007-01-04 | 2999-01-01 | 2007-10-01     |          1 |               2 |
+------------+------------+----------------+------------+-----------------+

 

If you look at the first record, it has a recurrence of 1 and a recurrence type of 1 - this means each day this item should get charged.

So basically, in pseudo code, the SQL statement would be:

SELECT * FROM recurring_invoices WHERE last_occurrence + INTERVAL recurrence (as value: 1,2,3 - etc) recurrence_type (as type, 1=day,2=month,3=year) = CURDATE()

[fenway]

You can use a bunch of IF() statements to determine what INTERVAL to add.

[SupaMonkey]

This is the solution I came up with; thanks for leading me in the right direction

SELECT
start, end, recurring_invoice_products.id as recurring_invoice_product_id, product,last_occurrence,
CASE 
	WHEN recurrence_type=1 THEN 
	(last_occurrence + INTERVAL recurrence DAY)
	WHEN recurrence_type=2 THEN
	(last_occurrence + INTERVAL recurrence MONTH)
	ELSE
	(last_occurrence + INTERVAL recurrence YEAR)
END as next_occurrence
FROM
recurring_invoice_products,products,product_types 
WHERE
product_types.id=products.product_type_id AND recurring_invoice_products.product_id=products.id AND end > NOW() 
HAVING
next_occurrence <= CURDATE()
ORDER BY product

[fenway]

CASE will work too -- nicely done; btw, I would suggest proper join syntax.

[SupaMonkey]

Thanks

Whats improper about how I've done it? Please correct me

 

Here is the single update SQL statement thats updates the 'last_occurrence' to have the 'next_occurrence' value if anyones interested:

UPDATE
recurring_invoice_products
SET
last_occurrence = 
CASE 
	WHEN recurrence_type=1 THEN 
	(last_occurrence + INTERVAL recurrence DAY)
	WHEN recurrence_type=2 THEN
	(last_occurrence + INTERVAL recurrence MONTH)
	ELSE
	(last_occurrence + INTERVAL recurrence YEAR)
END
WHERE
end > NOW() AND 
(CASE 
WHEN recurrence_type=1 THEN 
(last_occurrence + INTERVAL recurrence DAY)
WHEN recurrence_type=2 THEN
(last_occurrence + INTERVAL recurrence MONTH)
ELSE
(last_occurrence + INTERVAL recurrence YEAR)
  END) <= CURDATE()

[fenway]

Thanks

Whats improper about how I've done it? Please correct me

 

FROM
recurring_invoice_products
INNER JOIN products ON ( products.id=recurring_invoice_products.product_id )
INNER JOIN product_types ON ( product_types.id=products.product_type_id )
WHERE end > NOW() 

[SupaMonkey]

Thanks fenway

Whats the difference/implications/etc?

[fenway]

Thanks fenway

Whats the difference/implications/etc?

First, it's easier to read -- which means easier to debug -- really hard to miss a JOIN condition this way.

Second, it "forces" the optimizer to pick the right conditions for the JOIN.

[SupaMonkey]

Thanks fenway,

Will do it like that from now on