CODEWITHSUNDEEP
mysqldatedatetime
Kannan
Kannan

Reputation: 21

Dynamic due date finder in a single query

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

Upvotes: 0

Views: 58

Answers (1)

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 0

Related Questions