1
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

Kannan
  • 21
  • 2
  • 4
    I removed the superfluous database tags. Your code is clearly Oracle. Only tag with the database you are really using. – Gordon Linoff Aug 24 '18 at 11:45
  • You should really consider using whitespace when writing SQL. That code is incredibly hard to read as it is. You're also more likely to get an answer from the volunteers here if it's well formatted, as it makes it much easier for them to do so. – Thom A Aug 24 '18 at 11:46
  • The code is clearly Oracle, but yet you state in your question that you are using MySQL 5.7 - what is it really? And if it is MySQL, then where is the MySQL query? –  Aug 24 '18 at 11:48
  • Please add the expected output from above table also. – Ankit Bajpai Aug 24 '18 at 12:44
  • Pardon me for not being clear about the question. Actually I'm totally new to forums. I am a plsql developer. New to mysql. This question is purely MySQL. So sorry to tag it in Oracle. I changed it now. Kindly help me with MySQL query. – Kannan Aug 24 '18 at 21:39

1 Answers1

1

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 |
+----+-------------+----------+--------+-------------+
Patrick H
  • 653
  • 6
  • 14
  • I think this answer is not what I am looking for. (Ex. If i want to check due-dates between 2020-02-26 & 2020-03-28, this query will fail to give results right.) I want it to be dynamic. – Kannan Aug 27 '18 at 08:21