Sub-total and total columns
- by Tass-man
In Visual Foxpro 9 I am trying to wite an sql with a product "subtotal" column and a report "total" column. The sql code that works is as follows, but when I insert the commented out "Case" code I get errors that seem to increase as I correct the preceeding error. Can any one tell me in which place I should insert the "case" and what is wrong with the code?
SELECT qItemSaleLines.ItemID, ;
qItems.ItemID, ;
qItemSaleLines.SaleID, ;
qSales.SaleID, ;
qSales.CardRecordID, ;
qCustomers.CardRecordID, ;
qItems.ItemNumber AS ProdCODE, ;
qItems.ItemName AS StkNAME, ;
qCustomers.LastName AS CUSTOMER, ;
qSales.InvoiceNumber AS SaleINVNo, ;
qSales.InvoiceDate AS SaleDATE, ;
qItemSaleLines.Quantity AS SaleQTY, ;
qItemSaleLines.TaxExclusiveTotal AS SALE, ;
qItemSaleLines.CostOfGoodsSoldAmount AS COGS, ;
qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount AS MARGIN, ;
(qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount) * / qItemSaleLines.TaxExclusiveTotal AS MPERCENT ;
FROM qItemSaleLines, qItems, qSales, qCustomers ;
WHERE qSales.CardRecordID = qCustomers.CardRecordID AND qItemSaleLines.SaleID = qSales.SaleID AND ;
qItemSaleLines.ItemID = qItems.ItemID AND qSales.InvoiceDate {^2009-06-30} ;
ORDER BY qItems.ItemNumber, qSales.InvoiceDate ;
*!* (SELECT qItems.ItemID, qItemSaleLines.ItemID, qItemSaleLines.TaxExclusiveTotal, ;
*!* CASE WHEN qItems.ItemID = (SELECT TOP 1 qItems.ItemID FROM qItems.ItemID, ;
*!* WHERE qItems.ItemID = qItemSaleLines.ItemID, ;
*!* ORDER BY qItems.ItemID desc), ;
*!* THEN (SELECT SUM(qItemSaleLines.TaxExclusiveTotal) FROM qItemSaleLines.TaxExclusiveTotal,;
*!* WHERE qItems.ItemID <= qItemSaleLines.ItemID AND qItems.ItemID = qItemSaleLines.ItemID, ;
*!* ELSE ' ' END AS 'PROD-SALE'), ;
*!* CASE WHEN qItems.ItemID = (SELECT TOP 1 qItems.ItemID FROM qItems.ItemID, ;
*!* ORDER BY qItems.ItemID desc), ;
*!* THEN (SELECT SUM(qItemSaleLines.TaxExclusiveTotal) FROM qItemSaleLines.TaxExclusiveTotal, ;
*!* ELSE ' ' END AS 'Grand Total') ;
