CODEWITHSUNDEEP
mysqlsqldatabasedatetime
Kannan
Kannan

Reputation: 21

Dynamic due dates checking with the given period of dates

id  start_date  interval  period
1   1/22/2018   2         month 
2   2/25/2018   3         week  
3   11/24/2017  3         day   
4   7/22/2017   1         year  
5   2/25/2018   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). 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.

Here what i have done to resolve this. I am using mysql 5.7. I found ways 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 and its a performance killer and there is restriction of population of records.(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(in oracle)

 select id 
   from (select a.*,
           case 
           when period='week' 
           then mod((to_date('22-07-2018','dd-mm-yyyy')-start_date),7*interval)  
           when period='month' and to_char(to_date('22-07-2018','dd-mm-yyyy'),'dd')=to_char(start_date,'dd') 
                and mod(months_between(to_date('22-07-2018','dd-mm-yyyy'),start_date),interval)=0 
            then 0
            when period='year' and to_char(to_date('22-07-2018','dd-mm-yyyy'),'dd-mm')=to_char(start_date,'dd-mm') 
             and mod(months_between(to_date('22-07-2018','dd-mm-yyyy'),start_date)/12,interval)=0 
            then 0 
            when period='day' 
             and mod((to_date('22-07-2018','dd-mm-yyyy')-start_date),interval)=0 
            then 0 else 1 end filter from kml_subs a) 
            where filter=0;

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

Thanks, Kannan

Upvotes: 1

Views: 82

Answers (1)

Patrick H
Patrick H

Reputation: 653

Assuming this is an Oracle question and not MySQL:

I think the first thing that you need to do is calculate when the due date is. I think a simple case statement can handle that for you:

case when period = 'day' then start_date + numtodsinterval(interval,period)
     when period = 'week' then start_date + numtodsinterval(interval*7,'day')
     when period = 'month' then add_months(start_date,interval)
     when period = 'year' then add_months(start_date,interval*12)
end due_date

Then, using that new due_date field, you can check if the due date falls between the desired date range.

select *
from(
select id,
       start_date,
       interval,
       period,
       case when period = 'day' then start_date + numtodsinterval(interval,period)
            when period = 'week' then start_date + numtodsinterval(interval*7,'day')
            when period = 'month' then add_months(start_date,interval)
            when period = 'year' then add_months(start_date,interval*12)
            else null end due_date
from data)
where due_date between date '2018-02-25' and date '2018-03-12'

The above query checking between 2/25/18 and 3/12/18 produces the following output using your data:

+----+-------------+----------+--------+-------------+
| id | start_date  | interval | period |  due_date   |
+----+-------------+----------+--------+-------------+
|  2 | 05-FEB-2018 |        3 | week   | 26-FEB-2018 |
|  5 | 25-FEB-2018 |        2 | week   | 11-MAR-2018 |
+----+-------------+----------+--------+-------------+

Upvotes: 1

Related Questions