I'm hoping someone could drop me a few hints for finding missing data with
SQL. We have an accounting table that is supposed to keep a running record
of each year that a particular item remains 'active'. Dates are stored as
8-character strings, so the data looks something like this:

ITEM DATE_STR
xxx 20041221
xxx 20031221 <-- 2002 is a
xxx 20011221 <-- missing entry
yyy 20040618
yyy 20030618
zzz 20050712
zzz 20040712
zzz 20030712
....

What we'd like to be able to find is all ITEMs where one or more years are
missing. Any idea where to begin would be great...!

Thanks,
Terry

--
Disclaimer: Any comments made are personal and do not reflect the thoughts
or policies of my employer.

Alder wrote:

which database engine and version?

Michael Austin wrote::
Sorry, I have this bad habit of leaving out the important bits :-(

Here's our environment:

-- Network and OS versions --------
Process Software MultiNet V4.4 Rev A-X
AlphaServer ES40
OpenVMS AXP V7.3-1

-- Rdb and SQL-client versions ------
Executing RMU for Oracle Rdb V7.0-7
Current version of SQL is: Oracle Rdb SQL V7.0-7

Thanks,
Terry

See if this works:
select distinct
t.item
from
item_table t
where
-- number of distinct years for this item
(select count(distinct(cast(t1.date_str as char(4))))
from item_table t1
where t1.item = t.item)
-- different from year span (max minus min plus 1)
<> (select cast(cast(max(t2.date_str) as char(4)) as integer)
from item_table t2)
- (select cast(cast(min(t3.date_str) as char(4)) as integer)
from item_table t3)
+ 1
;



"Alder" <MUNDDGNTDYTV (AT) spammotel (DOT) com> wrote

David wrote::

Looks nice, David. I'll run it against my table when I get back to work
Monday/Tuesday. Thanks for the great effort!

Alder

David,

I tried the SQL SELECT statement you provided, and it produced a result for
almost every record. Looking at the SELECT statemetns to the right of the
inequality operator it looked like these statements required their own WHERE
clause to limit them to the same record as was being processed by the SELECT
statement to the left of the inequality.

If this is correct in theory, I obviously got something wrong in the
practice, because the query is taking hours. This is what I tried:

---
select distinct
t.acct_title_no
from
title_accounting t
where
-- number of distinct years for this item
(select count(distinct(cast(t1.acct_pd_to_dt as char(4))))
from title_accounting t1
where t1.acct_title_no = t.acct_title_no)

-- different from year span (max minus min plus 1)
<> (select cast(cast(max(t2.acct_pd_to_dt) as char(4)) as integer)
from title_accounting t2
where t2.acct_title_no = t.acct_title_no)
- (select cast(cast(min(t3.acct_pd_to_dt) as char(4)) as integer)
from title_accounting t3
where t3.acct_title_no = t.acct_title_no)
+ 1
;

Thanks again,
Alder

"Alder" <PGDEHMKOKIMD (AT) spammotel (DOT) com> wrote

Alder wrote:
I have not reviewed the SQL, however I point out to you that performance and
logical correctness have no relationship to each other.

Dr. Dweeb