CODEWITHSUNDEEP
oracle-databasedatetimeplsql
breq
breq

Reputation: 25516

PL/SQL add 7 days to datetime and get something between that date

Ok, so i have database on PL/SQL.

Here is my sql question:

SELECT t.AC_NUMBER, t.DATE, a.comment_1, a.comment_2, a.comment_3, a.comment_4
  FROM proddba.cust_info t
       left join proddba.cust_descr a on a.ac_number=t.ac_number
 where a.open_date=(select min(b.open_date) 
                      from proddba.cust_descr b 
                     where b.ac_number=t.ac_number 
                       and b.open_date>=t.date 
                       and b.open_dane<=t.date+7days)

How to dynamically add +7 days to date?

And second, how to get only one date min(b.open_date) if there is two this same datas? Should I use distinct?

(select distinct min(b.open_date) 
   from proddba.cust_descr b 
  where b.ac_number=t.ac_number 
    and b.open_date>=t.date 
    and b.open_dane<=t.date+7days)

If have to get about 15000 records from database, is this should work?

Best Regards

Upvotes: 1

Views: 10283

Answers (1)

Justin Cave
Justin Cave

Reputation: 231761

You can simply add a number of days to a DATE 

t.date + 7

will add exactly 7 days to the DATE in t.date (so the time component will be preserved).

MIN will already cause the subquery to return a single data value-- there is no need to add a DISTINCT since it won't ever change the output. I'm not sure what problem you are trying to describe that results from getting multiple rows-- are you possibly concerned that the outer query returns two rows that have the same a.open_date value and you are trying to ensure that you get only one row?

Upvotes: 1

Related Questions