0
+------------------------+--------+
|  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

Note: mySQL version in my machine is 5.*

  • please avoid use of WITH as my mySQL version is lower than 8.* – Matee Gojra Jan 22 at 7:36
  • Please add this info into your question text into the same beginning. Proper tag added. – Akina Jan 22 at 7:38
  • You only want every 5th day? That's not what your example output shows. Or is that not output? – Rick James Jan 27 at 20:20
  • @RickJames i want every 5th day – Matee Gojra Jan 28 at 5:29
0
WITH RECURSIVE
cte AS 
( SELECT @starting_deviation num
  UNION ALL
  SELECT num + @delta_deviation
  FROM cte 
  WHERE num < @final_deviation )
SELECT * 
FROM invoices
JOIN cte ON invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY

For the query from the question @starting_deviation := 5, @delta_deviation := 5 and @final_deviation := 15. These values may be specified literally (inserted immediately into the query instead of variables), transferred via variables values assignement or taken from some service table.


In MySQL 5+ you must generate the numbers table in a subquery or use external static numbers table.

SELECT * 
FROM invoices
JOIN ( SELECT 5 num UNION ALL
       SELECT 10 UNION ALL
       SELECT 15 ) cte ON invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY

i want to add is my frequency is not upto 3 series. it can be more than 3 maybe any number.... so for example if n is 5 and i want 10 frquesnces means 5,10, 15, 20......, 50

Use numbers generator. For example it can be (shown generator may give up to 64 values):

SELECT * 
FROM invoices
JOIN ( SELECT 5 * (1 + t1.num + t2.num * 4 + t3.num + 16) num
       FROM (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
            (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
            (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t3
     ) cte ON  invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY
WHERE cte.num BETWEEN 1 AND 10;
  • WITH only works in mysql version 8.0 or higher. while I'm using mysql version 5.* – Matee Gojra Jan 22 at 7:35
  • 1
    @MateeGojra Updated with the code applicable to 5.x version. – Akina Jan 22 at 7:41
  • I have added version info... but one more thing i want to add is my frequency is not upto 3 series. it can be more than 3 maybe any number.... so for example if n is 5 and i want 10 frquesnces means 5,10, 15, 20......, 50 – Matee Gojra Jan 22 at 7:55
  • @MateeGojra Generate as many values as you need. Sample code added. – Akina Jan 22 at 8:13
0
WHERE (DATEDIFF(due_date, CURDATE) % 5) = 0

Will filter down to today and any multiple of 5 days before or after today. Use that as the basic building block for the rest of your query.

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.