🚀 go-pugleaf

Hi.

I think you need a SP here.

for
  select date
    from purchase

    union all

    select date
    from sales

    group by 1
  into :d
  /* now you have all the dates here */
do begin

  select sum(purchase_line.qty), sum(purchase_line.price *
purchase_line.qty),
  from purchase
    left join purchase_line on purchase_line.purchase_id =
purchase.purchase_id
  where
    purchase.date = :d
  into :purchase_qty, :purchase_amount;

  select sum(sales_line.qty), sum(sales_line.price * sales_line.qty)
  from sales
    left join sales_line on purchase_line.sales_id = sales.sales_id
  where
    sales.date = :d
  into :sales_qty, :sales_amount;

  /* be prepared to deal with NULLs in qty and amount here */

  /* calculate whatever needed */

end

And I would even combine both purchases and sales into single table with one
additional field like SIGN with +1 | -1 values to differentioate between two
types of operations. So you can create an index on date and get fast GROUP
BY speed. If so you could make the result in one pass ordering by dates and
summing appropiate variable depending on sign.


Maxim.


"Isaac" <no@no.com> ???????/???????? ? ???????? ?????????:
news:484db5e2@newsgroups.borland.com...
> Hello,
> I am looking for a logical and fast way to make the following
>
> I have 5 tables:
> 1- inventory items (item code, name, etc...)
>
> 2- purchased invoices (date, invoice no, etc...)
> 3- detail table for table purchased invoices to store invoices items
> (item code, quantity, price, etc...)
>
> 4- sold invoices (date, invoice no, etc...)
> 5- detail table for table sold invoices to store invoices items (item
> code, quantity, price, etc...)
>
> now I need to store all the items from table 1 into the following table
> ordered by date so I can calculate average cost
>
> 6- table average cost:
>
> Date        purchased qty           cost price            sold qty
> -----       --------------            ----------            ---------
> 5/6          50                             40.5                    0
> 7/6            40                           42
> 23      and so on
>
> I am thinking to for using insert into with select expression for table
> 2 and 3 but I need to also insert quantity sold for the same date how I
> can do that ?, again speed is critical
>
> Thanks