Randy,
I am by no means a SQL expert, so take some of this with a grain of salt. <g>
>how do i declare the table variable?I already showed you that in my last post (CREATE @TempPriorSales TABLE etc.etc.)
>can i create an identity column on the table variable?I never tried it, but I don't see why not.
>do i need to create an index on the table variable before i join its results with results from another table?Probably not (and I don't know if you can, but I never tried this either).
>also, do you see any optimization i could do for my queries? example: is there a way to calculate monthly sales and yearly sales per customer in one data pass?Here's where I don't have a definitive answer. I usually just play around with queries until I find something that works decently. If I were you, I'd get the queries working first, then I'd work on optimizing them.
~~Bonnie