CODEWITHSUNDEEP
mysqlsql
Rahman Haroon
Rahman Haroon

Reputation: 1145

How to get next payment date on SQL

I have two tables project_payment and payment_schedule

CREATE TABLE project_payment (
  `tenant_id` INTEGER,
  `project_id` INTEGER,
  `payment_date` datetime NOT NULL,
  PRIMARY KEY(tenant_id,project_id,payment_date)  
);

INSERT INTO project_payment
  (`tenant_id`, `project_id`, `payment_date`)
VALUES
  ('1', '1', '2020-12-11 07:09:16'),
  ('1', '1', '2020-12-15 07:09:16');

CREATE TABLE payment_schedule (
  `tenant_id` INTEGER not null ,
  `project_id` INTEGER not null,
  `schedule_date` datetime NOT NULL,
  PRIMARY KEY(tenant_id,project_id,schedule_date)
);

INSERT INTO payment_schedule
  (`tenant_id`, `project_id`, `schedule_date`)
VALUES
  ('1', '1', '2020-12-15 07:09:16'),
  ('1', '1', '2020-12-28 07:09:29'),
  ('1', '1', '2021-01-02 01:00:00');

Here I take last_payment_date, now i need to get the next payment schedule date

SELECT MAX(pp.payment_date) last_payment_date 
  FROM project_payment pp 
  JOIN payment_schedule ps 
    ON ps.tenant_id = pp.tenant_id 
   AND ps.project_id = pp.project_id;

I don't knw how to select the next date so I didn't write that code here..

I need to get next payment date from the schedule_date according to the current date(present day)

Expected output:

last_payment_date    next_schedule_date
2020-12-15 07:09:16  2020-12-28 07:09:29

Upvotes: 0

Views: 496

Answers (1)

Akina
Akina

Reputation: 42642

SELECT p.payment_date last_payment_date,
       s.schedule_date next_schedule_date
FROM ( SELECT MAX(payment_date) payment_date
       FROM project_payment ) p
JOIN payment_schedule s ON p.payment_date < s.schedule_date
ORDER BY s.schedule_date LIMIT 1

Upvotes: 1

Related Questions