I have a select statement that is calculating the subTotal, Tax, and grand
total:
UPDATE Invoice
SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM where
INVOICE_ID = @invoiceId),
Tax = (select round( sum(PRICE * QTY) * ( @tax / 100) + .005, 2) from
INVOICE_ITEM where INVOICE_ID = @invoiceId),
Total = (select round( sum(PRICE * QTY) * ( 1 + ( @tax / 100)) +
.005, 2) from INVOICE_ITEM where INVOICE_ID = @invoiceId),
updatedby = @updatedby
WHERE Invoice_Id = @invoiceId;
One thing I don't like is that each column contains the same basic inner
select. Can I optimize the query such that the Sub_Total is used in
calculating the Tax and Total:
UPDATE Invoice
SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM where
INVOICE_ID = @invoiceId),
Tax = round( Sub_Total * ( @tax / 100) + .005, 2)
Total = (round( Sub_Total * ( 1 + ( @tax / 100)) + .005, 2),
updatedby = @updatedby
WHERE Invoice_Id = @invoiceId;
P.S. I just added the Sub_Total column to the table and need to do a one
time calculation of ALL the Sub_Total columns, for some reason this is not
working:
UPDATE Invoice SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM as
i1 where i1.INVOICE_ID = INVOICE_ID),
updatedby = 'manual';
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
