+ Reply to Thread
Results 1 to 7 of 7

SQL to find missing entries

  1. SQL to find missing entries

    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.



  2. Re: SQL to find missing entries

    Alder wrote:

    > 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.
    >
    >


    which database engine and version?



  3. Re: SQL to find missing entries

    Michael Austin wrote::
    >
    > which database engine and version?
    >
    >


    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


  4. Re: SQL to find missing entries

    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" wrote in message news:<40183f66$1@obsidian.gov.bc.ca>...
    > 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


  5. Re: SQL to find missing entries

    David wrote::

    > 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
    > ;


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

    Alder

  6. Re: SQL to find missing entries

    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" wrote in message
    news:tOFXb.5622$Hy3.5465@edtnps89...
    > David wrote::
    >
    > > 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
    > > ;

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




  7. Re: SQL to find missing entries

    Alder wrote:
    > 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:
    >


    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
    > ---
    > 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" wrote in message
    > news:tOFXb.5622$Hy3.5465@edtnps89...
    >> David wrote::
    >>
    >>> 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
    >>> ;

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




+ Reply to Thread