In what I'm hoping is a fairly straightforward query for someone who knows what they're doing, I'm trying to retrieve customer spending summary details by month as follows:

CustomerID CustomerName JanSales FebSales MarSales ... DecSales

The relevant customer table ("custrec") fields are "cuscode" and "cusname", and the invoice table ("invoice") has the fields "date" and "invtot".

Now for an extra restriction: The database I'm using (Advantage Database Server via its ODBC driver) doesn't seem to allow derived tables (i.e. no nested SELECTs within the FROM clause).

At present, I've managed to get the query working by creating views for each month and combining them into a single query along the following lines:

CREATE VIEW janview AS
SELECT c.cuscode, SUM(i.invtot - i.invtax) as jansales
FROM custrec c, invoice i
WHERE c.cuscode = i.acccode
AND i.date >= '1/1/2003'
AND i.date <= '1/31/2003'
GROUP BY c.cuscode

SELECT c.cuscode, c.cusname, jansales, febsales, ...
FROM custrec c LEFT OUTER JOIN jansales jan ON c.cuscode = jan.cuscode LEFT OUTER JOIN febsales feb ON c.cuscode = feb.cuscode ...
ORDER BY c.cusname

This is working but it's impressively slow on the data I need to query. If anyone can offer a suggestion or two as to how to do this query more efficiently, I'd be most grateful.

Thank-you!

That's made an incredible difference to the speed of the query... 'takes about 1/7th the time to execute.