Re: SQL question

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 25 Dec 2006 19:20:41 -0800
Message-ID: <1167103241.057915.309980@42g2000cwt.googlegroups.com>

mpal wrote:
> Hello everyone,
>
> I need to write a query in SQL that returns specific records, I am not
> sure how to approach it.
> I was wondering if someone would have any suggestion.
>
> Here is the situation.
>
> Essentially, the table includes list of departments with number off
> attributes.
> Whenever there is a change in any of the attributes, new record with
> new effective date is created.
>
> The table includes the following records (illustrative sample):
>
> DEPT EFF_DATE DESCRIPTION OTHER
>
> 0100 Apr 21 Education Dept xxx
> 0100 Apr 22 Education Dept yyy
> 0100 Apr 23 Education Dept zzz
> 0100 Apr 24 Education Department zzz
> 0100 May 1 Education Dept aaa
> 0100 May 2 Education Dept bbb
> 0200 Apr 1 Research Dept xxx
> 0200 Apr 2 Research & Dev xxx
> 0200 Apr 3 Research & Dev yyy
> 0200 Apr 4 Research & Development yyy
> 0200 Apr 5 Research Dept yyy
>
> I need to write a query that will scan the table for each department in
> chronological order and select the records where the change occurred in
> the Description field.
>
> The expected result from the previous table would be:
>
> DEPT EFF_DATE DESCRIPTION OTHER
>
> 0100 Apr 21 Education Dept xxx
> 0100 Apr 24 Education Department zzz
> 0100 May 1 Education Dept aaa
> 0200 Apr 1 Research Dept xxx
> 0200 Apr 2 Research & Dev xxx
> 0200 Apr 4 Research & Development yyy
> 0200 Apr 5 Research Dept yyy
>
> Any suggestions would be appreciated.
>
> Thank you,
>
> Milos

Hints:
Investigate the LAG analytic function in the Oracle SQL reference manual, which allows access to values in previously read rows. Output should be permitted only when the current description is not the same as the previous description. The output will look like this:

DEPT  EFF_DATE    DESCRIPTION             OTHER
0100  21-APR-2006  Education Dept          xxx
0100  24-APR-2006  Education Department    zzz
0100  01-MAY-2006  Education Dept          aaa
0200  01-APR-2006  Research Dept           xxx
0200  02-APR-2006  Research & Dev          xxx
0200  04-APR-2006  Research & Development  yyy
0200  05-APR-2006  Research Dept           yyy

If hints are sufficient, do not read any further.

The set up:
CREATE TABLE T1(
  DEPT VARCHAR2(10),
  EFF_DATE DATE,
  DESCRIPTION VARCHAR2(30),
  OTHER VARCHAR2(10));

INSERT INTO T1 VALUES ('0100','21-APR-2006','Education Dept','xxx');
INSERT INTO T1 VALUES ('0100','22-APR-2006','Education Dept','yyy');
INSERT INTO T1 VALUES ('0100','23-APR-2006','Education Dept','zzz');
INSERT INTO T1 VALUES ('0100','24-APR-2006','Education

Department','zzz');

INSERT INTO T1 VALUES ('0100','01-MAY-2006','Education Dept','aaa');
INSERT INTO T1 VALUES ('0100','02-MAY-2006','Education Dept','bbb');
INSERT INTO T1 VALUES ('0200','01-APR-2006','Research Dept','xxx');
INSERT INTO T1 VALUES ('0200','02-APR-2006','Research & Dev','xxx');
INSERT INTO T1 VALUES ('0200','03-APR-2006','Research & Dev','yyy');
INSERT INTO T1 VALUES ('0200','04-APR-2006','Research &

Development','yyy');
INSERT INTO T1 VALUES ('0200','05-APR-2006','Research Dept','yyy');

COMMIT; Experimenting with the LAG analytic function: SELECT
  DEPT,
  EFF_DATE,
  DESCRIPTION,
  LAG(DESCRIPTION,1) OVER (PARTITION BY DEPT ORDER BY EFF_DATE) PREVIOUS_DESCRIPTION,
  OTHER
FROM
  T1
ORDER BY
  DEPT,
  EFF_DATE When separated by the DEPT column, and sorted by the EFF_DATE column, the LAG statement above retrieves the DESCRIPTION one row prior to the current row - this result is then aliased as PREVIOUS_DESCRIPTION.

If we were able to include analytic functions in the WHERE clause, it would be easy to determine if the current description differs from the previous description - but this is not permitted. The work around involves sliding the above into an inline view: SELECT
  DEPT,
  EFF_DATE,
  DESCRIPTION,
  OTHER
FROM
  (SELECT
    DEPT,
    EFF_DATE,
    DESCRIPTION,
    LAG(DESCRIPTION,1) OVER (PARTITION BY DEPT ORDER BY EFF_DATE) PREVIOUS_DESCRIPTION,
    OTHER
  FROM
    T1
  ORDER BY
    DEPT,
    EFF_DATE)
WHERE
  DESCRIPTION<>NVL(PREVIOUS_DESCRIPTION,' ');

Note the use of the NVL in the WHERE clause. This is required, otherwise the first row for each DEPT will be dropped as the PREVIOUS_DESCRIPTION will be null - if PREVIOUS_DESCRIPTION is null, we treat it as a single space.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Dec 25 2006 - 21:20:41 CST