I have a table where payments are located with
payment_id and also
payment_due_date => payment receiving date and
period_months that is total period of the payment
I have added 2 columns in this table named
service_end_date. I want to update these columns.
service_start_date will be the first payment date and
service_end_date will be payment_due_date+period_months. Then next
service_start_date will be
(payment_due_date+period_months)+1 day and its
service_end_date will be
service_start_date+period_month and so on for the next payments.
here is the queries to implement
CREATE TABLE `temp_table` ( `invoice_id` int(11) DEFAULT NULL, `payment_id` int(11) NOT NULL DEFAULT '0', `payment_due_date` datetime DEFAULT NULL, `period_months` varchar(255) DEFAULT NULL, `service_start_date` date DEFAULT NULL, `service_end_date` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; insert into `temp_table` (`invoice_id`, `payment_id`, `payment_due_date`, `period_months`, `service_start_date`, `service_end_date`) values ('2065','3337','2015-12-15 00:00:00','3',null,null), -- '' is not a valid date ('2065','3338','2016-03-14 00:00:00','3',null,null), ('2065','3339','2016-06-12 00:00:00','3',null,null), ('2065','3340','2016-09-10 00:00:00','3',null,null);
select * from temp_table returns this:
What I want is this:
Note: There are multiple invoices in this table and there are multiple payments for each table. I need this to be solve in one single query.
If you have further questions and queries, then please comment.
How can I do this?