Hi All,

I'm stumped on this one, though the answer is probably staring me in  
the face, so I hope someone can help.

I have three tables (simplified here): Invoices, Orders and Moves.  
Each Invoice to a customer is for one or more products moved to them  
(ie sold to them). Each one of those products is moved (ie bought)  
from a supplier, incurring one or more Orders to satisfy each Invoice.

create table "Invoices"                 -- invoice to a customer for goods
(
          ID integer primary key
        , Delivery real                 -- price charged to customer for 
delivery
)
;

create table "Orders"                   -- order of goods from a supplier
(
          ID integer primary key
        , Delivery real                 -- cost of delivery for the order
)
;
create table "Moves"                    -- movement of product from supplier to 
customer
(
          ID integer primary key
        , Code text                     -- product code
        , Quantity integer              -- number of units of this product
        , Invoice integer               --> Invoices.ID
        , "Order" integer               --> Orders.ID
        , Sell real                     -- sell price as shown in the invoice
        , Buy real                      -- buy price as shown in the order
)
;

For instance, I invoice a customer for 5 products, which I order from  
three different suppliers, like this:

insert into "Invoices" values ( 10001, 22.0 );
insert into "Orders" values ( 11017, 12.0 );
insert into "Orders" values ( 11018, 10.0 );
insert into "Orders" values ( 11021, 15.0 );
insert into "Moves" values ( 28, 'CMH300',    1, 10001, 11017, 39.0,  
31.47 );
insert into "Moves" values ( 29, 'IGS032',    1,  10001, 11017, 98.0,  
79.51 );
insert into "Moves" values ( 30, '10027',     1,  10001, 11018, 98.0,  
80.0 );
insert into "Moves" values ( 31, 'APKNANIN',  1,  10001, 11018, 13.0,  
10.0 );
insert into "Moves" values ( 32, 'GG1501007', 1,  10001, 11021, 10.0,  
8.0 );

I can calculate the total Income per Invoice by:

select "Invoices".ID as Invoice, Delivery, sum(Sell), Delivery +  
sum(Sell) as Income
from "Invoices" left join "Moves" on "Invoices".ID = "Moves".Invoice
group by Invoice
;

which gives:

Invoice   Delivery  sum(Sell)  Income
10001         22.0      258.0   280.0

and the total Expense per Order by:

select "Orders".ID as "Order", Delivery,  sum(Buy), Delivery +  
sum(Buy) as Expense
from "Orders" left join "Moves" on "Orders".ID = "Moves"."Order"
group by "Order"
;

which gives:

Order   Delivery  Sum(Buy)  Expense
11017       12.0    110.98   122.98
11018       10.0     90.0    100.0
11021       15.0      8.0     23.0

I want to get the total Expense per Invoice. How can this be done  
correctly and efficiently to give:

Invoice   sum(Buy)   sum("Orders".Delivery)  Expense
10001     208.98         37.0                245.98

I can get the total Expense (without Delivery) for the product Moves:

select "Invoices".ID as Invoice, sum(Buy)
from "Invoices" left join "Moves" on "Invoices".ID = "Moves".Invoice
group by Invoice
;

which gives:

Invoice   sum(Buy)
10001     208.98

If I try to include the Delivery like this:

select "Invoices".ID as Invoice, sum(Buy), sum("Orders".Delivery)
from "Invoices"
        left join "Moves" on "Invoices".ID = "Moves".Invoice
        left join "Orders" on "Moves"."Order" = "Orders".ID
group by Invoice
;

it gives an incorrect sum of Delivery, since it adds the delivery per  
Move instead of per Order:

Invoice   sum(Buy)   sum("Orders".Delivery)
10001     208.98         59.0

I tried this:

select
          "Invoices".ID as Invoice
        , (select sum(Buy) from Moves where Moves.Invoice = Invoices.ID) as  
"Sum Buy"
        , (select sum(Delivery) from "Orders" on "Orders".ID in (select  
"Order" from Moves where Invoice = Invoices.ID)) as "Sum Delivery"
from "Invoices"
group by Invoice
;

which works correctly but it seems inelegant, inefficient and in one  
test seems orders of magnitude too slow.

Any ideas?

Thanks,
Tom

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to