Query for effective date based results for a time frame

Question

Here is the table structure format for which I am trying to create query:

ProductName | Effective_Date |version_number
---------------------------------------------
Product1       01-Jan-02          1
Product1       05-Oct-03          2
Product1       03-Mar-04          3
Product1       12-Oct-04          4
Product1       04-May-05          5
Product1       15-Sep-06          6
Product2       01-Jun-02          1
Product2       03-Sep-08          2
Product3       04-Jun-10          1
Product3       05-Sep-11          2
Product4       16-Jun-03          1
Product4       17-Oct-03          2
Product5       07-Feb-05          1
Product6       01-Jun-02          1
Product6       03-Dec-05          2
Product7       03-Feb-04          1
Product7       05-Nov-10          2

I need to find out version which are effective between 2004-2005. Effective dates for version is determined as :

For Product 1, version 1 is effective from 01-Jan-2002 to 04-Oct-2003 and version 2 is effective from 05-Oct-2003 to 02-Mar-2004 and so on.

So effectiveness of version is from effective date to next version create date -1.

There may be diiferent cases for this:

1. There is no version for which effective date is in 2004-2005 so in this case last version with date < 2004 will be effective
2. Version is in 2004-2005, like if effective date is 25 Mar 2004 then there should be version effective till 24-Mar-2004.

Please help me to implement the query for this.

Answer 1

Say your table is called i:

SELECT 
i.PRODUCTNAME
, I.VERSION_NUMBER
, I.EFFECTIVE_DATE START_DATE
, i2.effective_date end_date  
from i 
left join i i2 on i2.version_number = i.version_number +1 and i2.productname = i.productname
where NOT ((I.EFFECTIVE_DATE <= '01/01/2004' AND nvl(i2.effective_date,sysdate) <= '01/01/2004') OR  (I.EFFECTIVE_DATE >= '12/31/2005' AND nvl(i2.effective_date,sysdate) >= '12/31/2005'))

I created start and end dates just using the effective_date of the next version up for matching products (assuming you dont skip versions, if you did would require parition/ordering).

There are two circumstances that you want to avoid. A given product/version started and stopped before 1/1/2004 or started and stopped after 12/31/2005. Any other combo of start/end dates you want to keep.

We use sysdate for latest versions with no end date

here is a functional example

Answer 2

The query below will show all the versions that were "in force" between January 1, 2004 and December 31, 2005. It does not show Products that did not have any version in effect during that time interval; if you need to show all the products, including these ones, you will need a left outer join to your "products" table (assuming it's a separate, smaller one).

with
     test_data ( ProductName, Effective_Date, version_number ) as (
       select 'Product1', to_date('01-Jan-02', 'dd-Mon-rr'), 1 from dual union all
       select 'Product1', to_date('05-Oct-03', 'dd-Mon-rr'), 2 from dual union all
       select 'Product1', to_date('03-Mar-04', 'dd-Mon-rr'), 3 from dual union all
       select 'Product1', to_date('12-Oct-04', 'dd-Mon-rr'), 4 from dual union all
       select 'Product1', to_date('04-May-05', 'dd-Mon-rr'), 5 from dual union all
       select 'Product1', to_date('15-Sep-06', 'dd-Mon-rr'), 6 from dual union all
       select 'Product2', to_date('01-Jun-02', 'dd-Mon-rr'), 1 from dual union all
       select 'Product2', to_date('03-Sep-08', 'dd-Mon-rr'), 2 from dual union all
       select 'Product3', to_date('04-Jun-10', 'dd-Mon-rr'), 1 from dual union all
       select 'Product3', to_date('05-Sep-11', 'dd-Mon-rr'), 2 from dual union all
       select 'Product4', to_date('16-Jun-03', 'dd-Mon-rr'), 1 from dual union all
       select 'Product4', to_date('17-Oct-03', 'dd-Mon-rr'), 2 from dual union all
       select 'Product5', to_date('07-Feb-05', 'dd-Mon-rr'), 1 from dual union all
       select 'Product6', to_date('01-Jun-02', 'dd-Mon-rr'), 1 from dual union all
       select 'Product6', to_date('03-Dec-05', 'dd-Mon-rr'), 2 from dual union all
       select 'Product7', to_date('03-Feb-04', 'dd-Mon-rr'), 1 from dual union all
       select 'Product7', to_date('05-Nov-10', 'dd-Mon-rr'), 2 from dual
     )
--  End of test data (NOT part of the SQL query). Query begins BELOW THIS LINE.
select productname, effective_date, to_date - 1 as to_date, version_number
from ( select productname, effective_date,
              lead(effective_date, 1, date '2099-01-01') 
                 over (partition by productname order by effective_date) as to_date,
              version_number
       from   test_data
     )
where effective_date <  date '2006-01-01'
  and to_date        >= date '2004-01-01'
;

Output:

PRODUCTNAME  EFFECTIVE_DATE  TO_DATE         VERSION_NUMBER
------------ --------------- --------------- --------------
Product1     05-Oct-03       02-Mar-04                    2
Product1     03-Mar-04       11-Oct-04                    3
Product1     12-Oct-04       03-May-05                    4
Product1     04-May-05       14-Sep-06                    5
Product2     01-Jun-02       02-Sep-08                    1
Product4     17-Oct-03       31-Dec-98                    2
Product5     07-Feb-05       31-Dec-98                    1
Product6     01-Jun-02       02-Dec-05                    1
Product6     03-Dec-05       31-Dec-98                    2
Product7     03-Feb-04       04-Nov-10                    1

10 rows selected.