Get rows with difference of dates being one

Question

I have the following table and rows defined in SQLFiddle

I need to select rows from products table where difference between two rows start_date and nvl(return_date,end_date) is 1. i.e. start_date of current row and nvl(return_date,end_date) of previous row should be one

For example

PRODUCT_NO TSH098 and PRODUCT_REG_NO FLDG, the END_DATE is August, 15 2012 and PRODUCT_NO TSH128 and PRODUCT_REG_NO FLDG start_date is August, 16 2012, so the difference is only of a day.

How can I get the desired output using sql.

Any help is highly appreciable.

Thanks

Answer 1

You can use lag analytical function to get access to a row at a given physical offset prior to the current position. According to your sorting order it might look like this(not so elegant though).

select *
  from products p
  join (select *
         from(select p.Product_no
                   , p.Product_Reg_No
                   , case 
                       when (lag(start_date, 1, start_date) over(order by product_reg_no)-
                            nvl(return_date, end_date)) = 1 
                            then lag(start_date, 1, start_date) 
                                   over(order by product_reg_no) 
                        end start_date
                   , End_Date
                   , Return_Date
               from products p 
              order by 2,1 desc
              )
        where start_date is not null
       ) s
    on (p.start_date = s.start_date or p.end_date = s.end_date)
 order by 2, 1 desc

SQL FIddle DEMO

Answer 2

In SQL, date + X adds X days to the date. So you can:

select  * 
from    products
where   start_date + 1 = nvl(end_date, return_date)

If the dates could contain a time part, use trunc to remove the time part:

select  * 
from    products
where   trunc(start_date) + 1 = trunc(nvl(end_date, return_date))

Live example at SQL Fiddle.