Hi all,

i have a master detail relationship, 2 tables "orders" and "lines" where orders are
stored. each record in lines stands for 1 item of the order, quite normal approach. each
line has a field "quantity". I most cases the supplier delivers the quantity that was
ordered. In some cases he delivers more than the ordered quantity.
In this case i mark the line with the order quantity as "delivered" and insert a copy of
this line where i set the quantity to the value of pieces that are over the ordered amount
and mark it as "over". Example: ordered: 10 supplied: 12

the lines-Table
OrderID  ItemID  Quantity  Status
19              78         10       delivered
19              78           2       over

now i want evalute the supplies this way

Item  QuantOrdered QuantDelivered Difference
78            10                  12                    2

i tried some SQL Satements but dont get what i want. i also have the chance to do it with
fastrport where i need a query as databasis (?).

One problem i see is that item 78 could be a lot of times in lines-table, with correct
quantity and with over quantity. so you have to combine the 2 lines of the same order.

What's your idea?  

Paul


I'm sure its possible to do it more elegantly (Robert will show us) but here's a script that works - I think

select itemid, 0 as quantordered, sum(quantity) as quantdelivered,0 as difference into memory\m1 from new
group by itemid;

select itemid, sum(quantity)as qty into memory\m2 from new where status = 'delivered'
group by itemid;

UPDATE "memory\m1" set quantordered = "memory\m2".qty
from "memory\m1"
left outer join "memory\m2" ON itemid = "memory\m2".itemid;

UPDATE "memory\m1" set difference = quantdelivered - quantordered;

select * from memory\m1;

Roy Lambert

@Roy

thank you for your quick response. beside i dont you if it would work correct it will not
work cause i use V2.12 in this application. In this release there is no FROM Clause in
UPDATE Statement. However, thank you anyway for inspiration.

>>UPDATE "memory\m1" set quantordered = "memory\m2".qty
>>from "memory\m1"
>>left outer join "memory\m2" ON itemid = "memory\m2".itemid;

Paul


Ouch. Its a looong time since I used V2, I don't have it installed anymore so sorry I can't help.

Roy Lambert

In this situation I always add a field to the lines table, so it becomes:

ID, Date, CustomerID, ProductID, QtyRequested, QtyDelivered, DateRequested, DateDelivered,
StatusID

in this case shipping staff actually enter the quantity deliveried into the table for
every line ... requiring them to count the product into the warehouse, which is a really
useful check.

This means that the "atom" of data (one delivery) is in one place.

With your structure (adding a second line to "balance" the requested / delivered) one
single item of delivery becomes 2 lines ... which I think is less elegant.

With my structure it is quite easy to write row-level constraints to check QtyRequested =
QtyDelivered, change statusID if they are not equal, and also run code for Short
deliveries or over-deliveries which are outside of acceptable bounds.

Adam