🚀 go-pugleaf

Notes:
it did not have to be this way, if I can get only the items that have 
transactions (purchases and sales) ordered by date will be faster but I 
have to test for each row if this is a purchase or a sale so I can do 
the math correclty, however I dont know how it can be done, any ideas 
will be appreciated I only need pseudo code and the general idea

Thanks

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

Maxim Shiryaev wrote:

>   select date
>     from purchase
> 
>     union all
> 
>     select date
>     from sales
> 
>     group by 1

	This isn't valid InterBase SQL.

Instead, do:

SELECT DISTINCT
  date
FROM
  purchase
UNION 
SELECT DISTINCT
  date
FROM
  sales
INTO ...

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better?  Use QC!
  http://qc.borland.com -- Vote for important issues

Craig Stuntz [TeamB] wrote:
> Maxim Shiryaev wrote:
>
>   
>>   select date
>>     from purchase
>>
>>     union all
>>
>>     select date
>>     from sales
>>
>>     group by 1
>>     
>
> 	This isn't valid InterBase SQL.
>
> Instead, do:
>
> SELECT DISTINCT
>   date
> FROM
>   purchase
> UNION 
> SELECT DISTINCT
>   date
> FROM
>   sales
> INTO ...
>
>   
Thanks

Maxim Shiryaev wrote:
> 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.
>
>
>
>   
>
>
>   
Thanks , but I did not understand how I can make the sign field

any function in InterBase to get the absolute value ? like abs() in delphi

Thanks

Isaac wrote:

> any function in InterBase to get the absolute value ? like abs() in
> delphi
> 
> Thanks

Have you checked the standard UDF library that comes with Delphi? IIRC
it contains an ABS function.

-- 
Bill Todd (TeamB)

I think Bill means InterBase not Delphi here. :-)

Bill Todd [TeamB] wrote:

> Have you checked the standard UDF library that comes with Delphi? IIRC
> it contains an ABS function.
> 

Quinn Wildman (CodeGear Developer Support) wrote:

> I think Bill means InterBase not Delphi here. :-)

Indeed I do.:)

-- 
Bill Todd (TeamB)

ok thanks

when I use for select with date field I automatically begin from the 
first of the table and the date in ascending ordered ?

for select <date field>
into :v_date
do  begin
.
.
.
end

If you are concerned about ordering you need to add an order by 
statement. Otherwise, ordering cannot be guaranteed.

Isaac wrote:
> when I use for select with date field I automatically begin from the 
> first of the table and the date in ascending ordered ?
> 
> for select <date field>
> into :v_date
> do  begin
> .
> .
> .
> end

"Isaac" <no@no.com> wrote in message news:484febae@newsgroups.borland.com...
> when I use for select with date field I automatically begin from the first 
> of the table and the date in ascending ordered ?
>
> for select <date field>
> into :v_date
> do  begin


When deal with SQL servers, always assume it is your responsibility to 
request the order you want. Just include an order by:

 for select <date field>
order by <date field>
into :v_date

Ascending is default, if you want descending, add "desc" following the field 
name.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

OK Thanks alot Quinn Wildman,
by the way I hope InterBase to become fully embedded that can run 
without installing server, is there a schedule for that ?

Wayne Niddery (TeamB) wrote:
> When deal with SQL servers, always assume it is your responsibility to 
> request the order you want. Just include an order by:
>
> for select <date field>
> order by <date field>
> into :v_date
>
> Ascending is default, if you want descending, add "desc" following the 
> field name.
>
Many thanks I will add order by to ensure ordering

one last question if I have union selects can I add one order by like this
order by 1

or I have to add order by after every union select

Thanks

Isaac wrote:

> one last question if I have union selects can I add one order by like
> this order by 1

Use a single ORDER BY at the end.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> Isaac wrote:
>
>   
>> one last question if I have union selects can I add one order by like
>> this order by 1
>>     
>
> Use a single ORDER BY at the end.
>
>   
ok sorry stupid question <g>