DavidM DavidM - 2 years ago 125
SQL Question

SQL select database library

How to print all readers, where time between last two borrows is more than 2 months?


name, surname, max(k1.borrow_date)
k_reader using(person_id)
k_rent_books k1 using(reader_id)
k_rent_books k2 using(reader_id)
months_between(add_months((k1.borrow_date),-2),k2.borrow_date) > 2
group by
name, surname, person_id
order by

But i dont know how to say that compare two last dates.
Thanks for help.

Answer Source

Due to some restrictions with the USING clause (e.g. ORA-25154), I had to switch the join syntax, but here's one option. Basically the way to find the last and second last borrow dates for a reader is as follows:

  1. Join to one copy of the K_RENT_BOOKS (K_RB1) table and finds the row with the latest BORROW_DATE for the current reader (from K_READER).
  2. Next, it joins to a second copy of K_RENT_BOOKS (K_RB2), again for the current reader and finds the latest BORROW_DATE that is not the one found in the first copy (K_FB1).
  3. Keep the resulting joined record if the last borrow date is two months after the 2nd last borrow date.


select k_p.name, k_rb1.borrow_date, k_rb2.borrow_date
  from k_person k_p
       inner join
       k_reader k_r 
          on k_p.person_id = k_r.person_id
       inner join
       k_rent_books k_rb1 
          on k_rb1.reader_id = k_r.reader_id
       inner join
       k_rent_books k_rb2 
          on k_rb2.reader_id = k_r.reader_id
 where k_rb1.borrow_date = (select max(borrow_date) 
                              from k_rent_books k_rb3 
                             where k_rb3.reader_id = k_r.reader_id
   and k_rb2.borrow_date = (select max(borrow_date)
                              from k_rent_books k_rb4
                             where k_rb4.reader_id = k_r.reader_id
                               and k_rb4.borrow_date <> k_rb1.borrow_date
   and months_between(k_rb1.borrow_date, k_rb2.borrow_date) > 2

There are other ways of doing this that may be faster (e.g. using a with clause that generates the last and second last borrow dates for all readers) but hopefully this provides a starting point.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download