1 of 1 people found this helpful
It is better to save SalesSummary field and an InvoiceMonthNumber as a record per month.
Since ExecuteSQL is very slow. ex. I tested date range 1 month
SELECT SUM(num) WHERE date<=? AND date>=?
finding about 3,000 from 360,000 records takes about 4sec.
You need 3years then 4x36sec may be need in this case.
Thank you for your suggestion. I assume I would:
1) create a script to find the records where the InvoiceMonthNumber is = or< n
2) then sort by date and go to last record and
3) set the Sales running total as a variable, looping through all the months to set successive repetitions of the variable.
4) finally, it would generate a single record per month in a new table populating the SalesSummary from the multiple repetitions of the variable. That will certainly be easy to graph.