CODEWITHSUNDEEP
mysqldatabasedatewhere-clause
Dev Matee
Dev Matee

Reputation: 5947

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+
|  Invoice_id | due_date | amount |
+-------------+----------+--------+
|  20         |2020-01-18|  1250  |
+-------------+----------+--------+
| 21          |2020-01-15|  1335  |
+-------------+----------+--------+

Get all Records with date passed n days and its multiple serires like below...

for example n=5

SELECT * FROM `invoices` 
WHERE `due_date = DATE_ADD(CURDATE() + INTERVAL 5 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 10 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 15 days)`

but i want to make it universal for any n value

Upvotes: 2

Views: 95

Answers (4)

Waleed Muaz
Waleed Muaz

Reputation: 802

WHERE (DATEDIFF(due_date, CURDATE) % 5) = 0

It'll select all the multiple of 5 before and after today...

Upvotes: 1

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

If you are using MySQL version 8.0 or higher, here is another alternative - 

WITH  CTE (RN, ID, DUE_DATE, AMT) AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY due_date) RN FROM Invoices
WHERE due_date >=CURDATE()
)
SELECT * FROM CTE WHERE RN%5=0

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

1 Way to achieve this is to generate a date range with 5 days difference and then join that with your table -

SELECT *
FROM `invoices` I
JOIN (SELECT a.Date
      FROM (SELECT CURDATE() + INTERVAL (a.a + (10 * b.a) + (100 * c.a) ) * 5 DAY as Date
            FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
            CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as b
            CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as c
           )a
      WHERE a.Date <= (SELECT MAX(due_date) FROM `invoices`)
     ) ON I.due_date = a.Date

I have generated only 1000 rows here. If your table is too large then you may generate 10000 rows using 1 more cross-join.

Upvotes: 2

Docker John
Docker John

Reputation: 100

You can create a variable and pass it into a prepared statement like below.

SET @dateinterval = n;

SET @preparedStatement = CONCAT("
SELECT * FROM `invoices`
WHERE `due_date` IN (
(CURDATE() + INTERVAL ",@dateinterval," day),
(CURDATE() + INTERVAL ",@dateinterval*2," day),
(CURDATE() + INTERVAL ",@dateinterval*3," day)
);
");

PREPARE SQLStatement FROM @preparedStatement;
EXECUTE SQLStatement;
DEALLOCATE PREPARE SQLStatement;

Provided u pass in 5 for the @dateinterval, the above statement will resolved as:

 SELECT * FROM `invoices`
 WHERE `due_date` IN (
 (CURDATE() + INTERVAL 5 day),
 (CURDATE() + INTERVAL 10 day),
 (CURDATE() + INTERVAL 15 day)
 );

Upvotes: 1

Related Questions