Dynamic due date finder in a single query

Question

id  start_date  interval  period
1   2018-01-22   2         month 
2   2018-02-25   3         week  
3   2017-11-24   3         day   
4   2017-07-22   1         year  
5   2018-02-25   2         week

the above is my table data sample. start_dates will be expired based on interval and period(i.e id-1 will have due date after 2 months from the start_date, id-2 will have due after 3 weeks vice versa). period is enum of (day,week,month,year). requirement is, Client can give any period of dates. let's say 25-06-2026 to 13-07-2026 like that.. I have to return the ids whose due dates falls under that period.I hope i made my question clear.

I am using mysql 5.7. I found a way to achieve this with recursive CTE's.(not available in mysql 5.7). and there is a way to achieve this by populating virtual records by using inline sub queries along with unions but its a performance killer and we can't do populate virtual records every time a client request comes.(like given in the link Generating a series of dates) I have reached a point to get results for a single date which is very easy. Below is my query.

  SELECT b.* 
FROM   (SELECT a.*, 
               CASE 
                 WHEN period = 'week' THEN MOD(Datediff('2018-07-22', start_date), 7 * intervals) 
                 WHEN period = 'month' 
                      AND Day('2018-07-22') = Day(start_date) 
                      AND MOD(Period_diff(201807, Extract(YEAR_MONTH FROM start_date)), intervals) = 0 THEN 0
                 WHEN period = 'year' 
                      AND Day('2018-07-22') = Day(start_date) 
                      AND MOD(Period_diff(201807, Extract( 
                              YEAR_MONTH FROM start_date)) / 12, 
                          intervals) = 0 THEN 0 
                 WHEN period = 'day' THEN MOD(Datediff('2018-07-22', start_date) , intervals) 
               end filters 
        FROM   kml_subs a)b 
WHERE  b.filters = 0;

But I need to do this for a period of dates not a single date. Any suggestions or solutions will be much appreciated.

My desired result shoud be like..

if i give two dates.say 2030-05-21 & 2030-05-27. due dates falls under those 6 dates between(2030-05-21 & 2030-05-27) will be shown in the result.

id
1
4

My question is different from Using DATE_ADD with a Column Name as the Interval Value . I am expecting a dynamic way to check due dates based on start_date

Thanks, Kannan

Can we just consider dates as sql dates - so y-m-d format throughout. It's too confusing otherwise? — Strawberry, Sep 24 '18 at 11:38
And given the data set above, what should the desired result look like? — Strawberry, Sep 24 '18 at 11:42
Yes. Due dates should be calculated dynamically by the query. That is my requirement — Kannan, Sep 24 '18 at 11:49
Possible duplicate of [Using DATE\_ADD with a Column Name as the Interval Value](https://stackoverflow.com/questions/22403945/using-date-add-with-a-column-name-as-the-interval-value) — Matt Raines, Sep 24 '18 at 11:53
We can find the next due date easily from my table. But it will be a headache when we try to find due dates for every record till 2030 or may be longer than that.. We should dynamically finds the due dates falls under the given period, like i got for a single date. My query will work for any date just we have to change the date. but i can't find a way to do it for period of dates. like to give the period in where condition. — Kannan, Sep 24 '18 at 12:02

score 0 · Answer 1 · answered Sep 24 '18 at 12:03

In MySQL, it would seem that a query along these lines would suffice. (Almost) everything else could and should be handled in application level code...

SELECT *
     , CASE my_period WHEN 'day'   THEN start_date + INTERVAL my_interval DAY 
                      WHEN 'week'  THEN start_date + INTERVAL my_interval WEEK 
                      WHEN 'month' THEN start_date + INTERVAL my_interval MONTH
                      WHEN 'year'  THEN start_date + INTERVAL my_interval YEAR
                      END due_date
  FROM my_table;

Hmm. Thanks for your response Strawberry. But I think here will be a way to do it.May be a very complicated query , But there should be a way right.? — Kannan, Sep 24 '18 at 12:11

Dynamic due date finder in a single query

1 Answers1