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

On Dec 25, 9:00 pm, "mpal" <mpalk... (AT) yahoo (DOT) com> wrote:
create table T (
dept char(4) not null,
eff_date date not null,
description varchar(30) not null,
primary key (dept, eff_date)
);

insert into T
values
('0100', '2006-04-21', 'Education Dept'),
('0100', '2006-04-22', 'Education Dept'),
('0100', '2006-04-23', 'Education Dept'),
('0100', '2006-04-24', 'Education Department'),
('0100', '2006-05-01', 'Education Dept'),
('0100', '2006-05-02', 'Education Dept'),
('0200', '2006-04-01', 'Research Dept'),
('0200', '2006-04-02', 'Research & Dev'),
('0200', '2006-04-03', 'Research & Dev'),
('0200', '2006-04-04', 'Research & Development'),
('0200', '2006-04-05', 'Research Dept');

select x.DEPT, x.EFF_DATE, x.DESCRIPTION from (
select t1.*,
(select count(1) from T t2 where t1.dept = t2.dept and
t1.eff_date > t2.eff_date) as rn
from T t1
) x where not exists (
select 1 from (
select t1.*,
(select count(1) from T t2
where t1.dept = t2.dept and t1.eff_date >
t2.eff_date) as rn
from T t1
) y where x.dept = y.dept and x.rn = y.rn + 1 and x.description =
y.description
)

You can compress the query a bit by creating a view for:

select t1.*,
(select count(1) from T t2 where t1.dept = t2.dept and
t1.eff_date > t2.eff_date) as rn
from T t1

or if your dbms supports CTE and row_number() function:

with enum (dept, EFF_DATE, DESCRIPTION,rn) as (
select dept, EFF_DATE, DESCRIPTION,
row_number() over (
partition by DEPT order by EFF_DATE
) from T T1
) select dept, EFF_DATE, DESCRIPTION
from enum e1
where not exists (
select 1 from enum e2
where e2.dept = e1.dept
and e2.rn = e1.rn - 1
and e2.DESCRIPTION = e1.DESCRIPTION
)

/Lennart

I explicitly specified schema name, because of many table T in forums.
Another example:
SELECT dept, eff_date, description
FROM mpal.T T1
WHERE NOT EXISTS
(SELECT *
FROM mpal.T T2
WHERE T2.dept = T1.dept
AND T2.eff_date
= (SELECT MAX(eff_date)
FROM mpal.T T3
WHERE T3.dept = T1.dept
AND T3.eff_date < T1.eff_date
)
AND T2.description = T1.description
);


If your DBMS supports OLAP functions:
SELECT dept, eff_date, description
FROM (SELECT T1.*
, MAX(description)
OVER(PARTITION BY dept ORDER BY eff_date
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS pre_desc
FROM mpal.T T1
) T2
WHERE description <> COALESCE(pre_desc, '');

You can use MIN instead of MAX in second example.

Tonkuma wrote:
[...]
Elegant, but there is a *minor* problem in using '' as a marker. For
example the row:

insert into T values ('0100','2006-04-20','')

will not be detected. One option is to use a marker than can never
appear, for example:

COALESCE(pre_desc, repeat('x',40))

/Lennart

Another way may be.
WHERE description <> pre_desc
OR pre_desc IS NULL;

Tonkuma wrote:




Yes - thanks so much both of you.