Re: Best way to do a join with effective dates and see all rows

Wow! I am now looking into the Lag and Lead stuff. It looks very interesting and could solve me issue.

Thank you for the help.

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1163689936.587149.254340_at_f16g2000cwb.googlegroups.com...
> Gummy wrote:
> > I know the tables look strange and you wouldn't see them in this format
> > normally. What I did was put it together in a simplified form for the
> > example.
> >
> > To my understanding having a table with two primary keys (the ProductID
and
> > EffectiveDate) is proper way of making a relational database that
changes
> > over time.
>
> I am having a hard time following what you are trying to do.
>
> I created a mock up:
> CREATE TABLE PRODUCTS (
> PRODUCTID NUMBER(12),
> PRODUCTNAME VARCHAR2(30),
> SUBGROUPID VARCHAR2(5),
> EFFECTIVEDATE DATE);
>
> CREATE TABLE SUBGROUPS (
> SUBGROUPID VARCHAR2(5),
> SUBGROUPNAME VARCHAR2(30),
> EFFECTIVEDATE DATE);
>
> INSERT INTO PRODUCTS VALUES (1,'Vanilla','A','01-JAN-2000');
> INSERT INTO PRODUCTS VALUES (1,'French Vanilla','A','01-JAN-2001');
> INSERT INTO PRODUCTS VALUES (1,'Yummy Vanilla','A','01-JAN-2003');
>
> INSERT INTO SUBGROUPS VALUES ('A','Frozen','01-JAN-2000');
> INSERT INTO SUBGROUPS VALUES ('A','Ice Cream','01-FEB-2001');
> INSERT INTO SUBGROUPS VALUES ('A','Dairy','01-JAN-2002');
>
> I then threw together a select using two inline views:
> SELECT
> P.PRODUCTID,
> P.PRODUCTNAME,
> S.SUBGROUPNAME,
> P.EFFECTIVEDATE,
> S.EFFECTIVEDATE
> FROM
> (SELECT
> SUBGROUPID,
> SUBGROUPNAME,
> EFFECTIVEDATE,
> (LAG(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))+1 AFTER_PREV,
> (LEAD(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))-1 BEFORE_NEXT
> FROM
> SUBGROUPS) S,
> (SELECT
> PRODUCTID,
> PRODUCTNAME,
> SUBGROUPID,
> EFFECTIVEDATE,
> (LAG(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))+1 AFTER_PREV,
> (LEAD(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))-1 BEFORE_NEXT
> FROM
> PRODUCTS) P
> WHERE
> (P.EFFECTIVEDATE BETWEEN S.EFFECTIVEDATE AND S.BEFORE_NEXT)
> OR (S.EFFECTIVEDATE BETWEEN P.EFFECTIVEDATE AND P.BEFORE_NEXT);
>
> This produces as output:
> PRODUCTID PRODUCTNAME SUBGROUPNAME EFFECTIVEDATE EFFECTIVEDATE
> 1 Vanilla Frozen 01-JAN-2000 01-JAN-2000
> 1 French Vanilla Frozen 01-JAN-2001 01-JAN-2000
> 1 French Vanilla Ice Cream 01-JAN-2001 01-FEB-2001
> 1 French Vanilla Dairy 01-JAN-2001 01-JAN-2002
>
> Explanation:
> SELECT
> SUBGROUPID,
> SUBGROUPNAME,
> EFFECTIVEDATE,
> (LAG(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))+1 AFTER_PREV,
> (LEAD(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))-1 BEFORE_NEXT
> FROM
> SUBGROUPS
>
> LAG and LEAD are used to determine the effective date range, LEAD is
> likely the only value of concern. This produces as output:
> SUBGROUPID SUBGROUPNAME EFFECTIVEDATE AFTER_PREV BEFORE_NEXT
> A Frozen 01-JAN-2000 (NULL) 31-JAN-2001
> A Ice Cream 01-FEB-2001 02-JAN-2000 31-DEC-2001
> A Dairy 01-JAN-2002 02-FEB-2001 (NULL)
>
> SELECT
> PRODUCTID,
> PRODUCTNAME,
> SUBGROUPID,
> EFFECTIVEDATE,
> (LAG(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))+1 AFTER_PREV,
> (LEAD(EFFECTIVEDATE,1) OVER (ORDER BY EFFECTIVEDATE))-1 BEFORE_NEXT
> FROM
> PRODUCTS;
>
> PRODUCTID PRODUCTNAME SUBGROUPID EFFECTIVEDATE AFTER_PREV BEFORE_NEXT
> 1 Vanilla A 01-JAN-2000 (NULL) 31-DEC-2000
> 1 French Vanilla A 01-JAN-2001 02-JAN-2000 31-DEC-2002
> 1 Yummy Vanilla A 01-JAN-2003 02-JAN-2001 (NULL)
>
> My best guess for how you intended to join the two tables:
> WHERE
> (P.EFFECTIVEDATE BETWEEN S.EFFECTIVEDATE AND S.BEFORE_NEXT)
> OR (S.EFFECTIVEDATE BETWEEN P.EFFECTIVEDATE AND P.BEFORE_NEXT)
>
> Products effective date must be between a subgroups effective date
> range, or subgroups effective date must be between a products effective
> date range. This does not exactly product the intended output, but you
> may be able to modify it as needed.
>
> (Hint: change the WHERE clause to handle nulls in the BEFORE_NEXT
> column:
> WHERE
> (P.EFFECTIVEDATE BETWEEN S.EFFECTIVEDATE AND
> NVL(S.BEFORE_NEXT,SYSDATE))
> OR (S.EFFECTIVEDATE BETWEEN P.EFFECTIVEDATE AND
> NVL(P.BEFORE_NEXT,SYSDATE));
> )
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>
Received on Thu Nov 16 2006 - 09:58:51 CST