I am trying to create a rolling unit sales history report(which will, in turn, forecast sales) from scratch using Commands. I determined I can't build this through conventional means because there will simply be too many formulas. Here is my previous post on the matter:

here

I've succeeded in pulling unit sales and creating a header for the field that specifies the data is for Sept 2011 (36 mos ago). Here is my SQL syntax for that:

SELECT "SalesTable"."UnitSalesQty" - "SalesTable"."UnitReturnQty" AS "NetQty36", DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AS "MM36", "ItemTable"."ItemNum"
FROM "ERP_app"."dbo"."SalesTable" "SalesTable"
INNER JOIN "ERP_app"."dbo"."ItemTable" "ItemTable" ON "ItemTable"."ItemKey"="SalesTable"."ItemKey"
WHERE "SalesTable"."SalesDate"
BETWEEN DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -35, GETDATE()-day(GETDATE()-1))-1 AND "ItemNum" = '1002'

I'm only selecting one item now for simplicity's sake.

My issue is, when I want to add Oct 2011, I don't know whether to add to this command or to create a new one. I've tried both. When I add to this command, I get error messages because the syntax with the date parameters is wrong. When I try create a new command with the "36's" changed to "35's" and add the data to the report, my Sept 2011 totals (which were right) end up too high.

I'm an accounting/finance guy by trade so please pardon any ignorance on my part.

Thanks for your help.

Rule of thumb when using commands - NEVER join multiple commands. When you do, Crystal will pull ALL of the data from every command into memory and do the join there. This can significantly slow down a report. When you can get everything into a single command, all of that work is pushed to the database where it will be more efficiently processed.

-Dell

When you say "NEVER join multiple commands" do you mean never even create multiple commands?

Thanks,

Well, the whole reason I ventured into using commands as opposed to building the report "normally" was because the number of records and number of formulas had brought this report to a screeching halt.

So, good to know. Thanks again.

Are commands handled the same way as stored procedures (at the server)? Will using a command instead of all those formulas speed this up? If not, I'm going to have to approach this a different way.

What software would I need to make a stored procedure? MySQL?

Thanks,