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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 16 Nov 2006 07:12:16 -0800
Message-ID: <1163689936.587149.254340@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:12:16 CST