I want to create a query that analyses the amount a customer has been invoiced compared to the total invoices for a month. As a bonus, we have a customer that skews the numbers and I would like to also show a total that excludes that customer.
The results would look like this:
The tables have these fields:
The customer to exclude has the CustNo of '0000001046'.
I have tried nested tables and it looks like it could work, but the joins appear convoluted and the whole thing is quite slow.
I don't think my example is a good query, so don't be afraid to toss out a new one.
SELECT customer.custname AS Customer, SUM(plmain.invtotal) AS CustInvoices, `MONTH`(plmain.invdate) AS DateMonth, `YEAR`(plmain.invdate) AS DateYear,
SUM(TotalSales.AllTotalSales) AS TotalSalesAll, SUM(TotalSalesNotInsitsu.NotTotalSales) AS TotalSalesNotInsitsu
FROM plmain, customer,
(SELECT SUM(invtotal) AS AllTotalSales, `MONTH`(invdate) AS AllDateMonth, `YEAR`(invdate) AS AllDateYear
FROM plmain plmain_2
GROUP BY AllDateMonth, AllDateYear) TotalSales,
(SELECT SUM(invtotal) AS NotTotalSales, `MONTH`(invdate) AS NotDateMonth, `YEAR`(invdate) AS NotDateYear
FROM plmain plmain_1
WHERE (custno <> '0000001146')
GROUP BY NotDateMonth, NotDateYear) TotalSalesNotInsitsu
WHERE plmain.custno = customer.custno AND plmain.invdate < GoMonth(customer.acct_date, 48) AND `MONTH`(plmain.invdate) = TotalSales.AllDateMonth AND
`YEAR`(plmain.invdate) = TotalSales.AllDateYear AND `MONTH`(plmain.invdate) = TotalSalesNotInsitsu.NotDateMonth AND `YEAR`(plmain.invdate)
GROUP BY customer.custname, DateYear, DateMonth, TotalSalesAll, TotalSalesNotInsitsu
ORDER BY customer.custname, DateYear, DateMonth