1

I have a table where payments are located with invoice_id and 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_start_date and 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:

enter image description here

What I want is this:

enter image description here

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?

1

I think that you want something like the following:

BEFORE:

('' is not a valid date - used null instead)

mysql> select * from temp_table;
+------------+------------+---------------------+---------------+--------------------+------------------+
| invoice_id | payment_id | payment_due_date    | period_months | service_start_date | service_end_date |
+------------+------------+---------------------+---------------+--------------------+------------------+
|       2065 |       3337 | 2015-12-15 00:00:00 | 3             | NULL               | NULL             |
|       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             |
+------------+------------+---------------------+---------------+--------------------+------------------+
4 rows in set (0.00 sec)
mysql> 

UPDATE QUERY:

UPDATE temp_table
SET 
service_start_date = date(payment_due_date), 
service_end_date = date(payment_due_date) + interval 3 month;
Query OK, 4 rows affected (0.10 sec)
Rows matched: 4  Changed: 4  Warnings: 0

And the result:

(I'm assuming that the service end_date is always 3 months after the payment_due_date).

mysql> select * from temp_table;
+------------+------------+---------------------+---------------+--------------------+------------------+
| invoice_id | payment_id | payment_due_date    | period_months | service_start_date | service_end_date |
+------------+------------+---------------------+---------------+--------------------+------------------+
|       2065 |       3337 | 2015-12-15 00:00:00 | 3             | 2015-12-15         | 2016-03-15       |
|       2065 |       3338 | 2016-03-14 00:00:00 | 3             | 2016-03-14         | 2016-06-14       |
|       2065 |       3339 | 2016-06-12 00:00:00 | 3             | 2016-06-12         | 2016-09-12       |
|       2065 |       3340 | 2016-09-10 00:00:00 | 3             | 2016-09-10         | 2016-12-10       |
+------------+------------+---------------------+---------------+--------------------+------------------+
4 rows in set (0.01 sec)

mysql>

[EDIT - in response to the OP's comments]

We need clearer explanations - clear input data, clear output data and (perhaps most importantly) the logic you use to get your derived data.

If you have complicated logic involved in the updates, you might consider using TRIGGERs - these can have a complex programming logic behind them. It is always better to perform data manipulation operations as close as possible to the data and as soon as possilbe - apart from direct SQL, nothing is closer or can be performed sooner.

You might also like to consider GENERATED colums (also known as COMPUTED and/or CALCULATED colums/fields - depending on your RDBMS). These can be VIRTUAL (i.e. calculated on the fly) or STORED (i.e. actually written to disk) - again, better than code external to the database and faster and more reliable.

The point is, if it's done in and controlled by the database server, all applications touching the database will work correctly for that particular field/values - whereas if you implement logic in your application, every application that touches that database will have to re-implement it (and require testing/debugging again and again). Since data tends to be far longer lived than IDEs/app development frameworks, it is vastly preferable to keep logic in the RDBMS rather than in developers' heads!

  • No this is not the result that i want, may be i did not explained well. service end_date is not always 3 months it can vary. It may be 2 3 4.... and the second problem is for payment_id 3337 it will be simple you made right dates but for next row that is 3338 it will be 1 day add in service end of 3337. Then 3338 service_start_date will be 2016-03-16 – Salman Shahid Apr 11 '16 at 9:22
  • 1
    Is the period_months the data we need to determine the gap between service_start_date and service_end_date? That information will have to be stored somewhere - either in the same table or elsewhere. SQL can't make guesses :-). You say It may be 2 3 4.... - how can I know? We need clearer explanations - clear input data, clear output data and (perhaps most importantly) the logic you use to get your derived data. – Vérace Apr 11 '16 at 9:36
  • Another thought just came to me. If you use MySQL 5.7, you can use computed fields - they may help? Also, if you're not using 5.7, have you considered TRIGGERs? – Vérace Apr 11 '16 at 9:46
  • Yes this is sure now this can not be done with one simple query i have involved PHP here. – Salman Shahid Apr 11 '16 at 12:32
  • @SalmanShahid of course it can be done in a single query. – ypercubeᵀᴹ Apr 11 '16 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.