Re: Compare 2 tables

From: <coral.ressources_at_gmail.com>
Date: Mon, 9 Mar 2009 05:44:24 -0700 (PDT)
Message-ID: <5cf9a037-adf6-425c-82b3-d17b9eaa3697_at_z1g2000yqn.googlegroups.com>

On Mar 9, 1:00 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> coral.ressour..._at_gmail.com schreef:
>
>
>
> > I have 2 tables: Input and Output.
> > Input table stores records in this manner:
> > recno, prod_first, prod_span, prod_last
> > 1, 1, 1000, 1000
> > 2, 1001, 700, 1700
> > 3, 1701, 100, 1800
> > ...and so on
> > prod_first is the id of the product,
> > prod_span is the number of products stored by recno,
> > prod_last is the last product stored by recno
>
> > Output tables stores records related to outgoings:
> > recno, prod_first, prod_span, prod_last
> > 1, 500, 25, 525
> > 2, 300, 100, 400
> > 3, 1243, 100, 1343
> > That mean productss no from 500 to 525, 300 to 400 and 1243 to 1343
> > were delivered, while the rest from input table are still in stock.
> > Question: how to find what is [still] in input table and not [yet] in
> > output table?
> > prod_first to prod_last are the items to be found in both tables.
> > I tought like this:
> > 1. make 2 temp tables: input_temp, output_temp where i would insert
> > all records [one-by-one] since in original tables i have only start &
> > end.
> > 2. make a comparison between the 2 temp tables (using SQL aggregation)
> > I am concerned that step 1 would hit the db performance as i have for
> > input table aprox 100 000 [and growing!] records to insert.
>
> In your system, is it possible to have as outgoing:
>
> 1, 1650,100,1750  ? So: overlap on boundaries of ingoing?
>
> Shakespeare

yes, we can have, but interval could not be overlapped; for instance: i can have as outgoing following records: 1, 500, 25, 525
2, 300, 100, 400
but not:
1, 500, 25, 525
2, 300, 300, 600
because 2. record would overlap something that is [already] out... Received on Mon Mar 09 2009 - 07:44:24 CDT