Coupla things, Randy ...

Have you tried running this in bits and pieces in Query Analyzer? IOW, does your first SELECT produce any results at all? Do the sub-selects within that first select produce results?

One thing that I've used extensively instead of the #TempTable kind of temp table is to use table variables. It's supposed to be much more efficient than the temp tables. The only drawback to using a table variable instead of a temp table is that they've only got local scope. So, you can't create one in one StoredProc and use it in another ... but you're not doing that in this StoredProc, so it's ok. The syntax for that would be something like this:

CREATE @TempPriorSales TABLE 
       (PriorYearRank  int, 
        CustomerNumber int, -- or varchar .. whatever it's supposed to be
        CustomerName   varchar(100), 
        PriorSales     money,
        PriorYearMonthlySales money)

Then you can use INSERT INTO @TempPriorSales etc.etc.etc.

But, anyway, that's just minor tweaking. First, you gotta get those SELECTs running correctly!! <g>

~~Bonnie


>ok here is my procedure with my queries, but it is not returning any results. did i leave something out? thanks.
>
>

>CREATE PROCEDURE Top300Customers AS
>
>/* Get Prior Year Customer Totals */
>IF EXISTS(Select OBJECT_ID('[tempdb].[dbo].[TempPriorSales]'))
>BEGIN
>	DROP TABLE TempPriorSales
>END
>
>
>/* Get Prior Year Customer Totals */
>Select IDENTITY(INT, 1, 1) as PriorYearRank, PriorSales.CustomerNumber,
>	PriorSales.CustomerName, PriorSales.PriorSales,
>	PriorYearByMonth.PriorYearMonthlySales INTO #TempPriorSales
>From
>   (
>	Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorSales
>	From slsMain
>	Where InvoiceDate >= '01/01/2003' and InvoiceDate < '01/01/2004'
>	Group By CustomerNumber, CustomerName
>   ) PriorSales
>   LEFT OUTER JOIN
>   (
>	Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) PriorYearMonthlySales
>	From slsMain
>	Where InvoiceDate >= '09/01/2003' and InvoiceDate < '10/01/2003'
>	Group By CustomerNumber, CustomerName
>   ) PriorYearByMonth
>   ON PriorSales.CustomerNumber = PriorYearByMonth.CustomerNumber and
>	PriorSales.CustomerName = PriorYearByMonth.CustomerName
>Order By PriorSales.PriorSales DESC
>
>
>
>DECLARE @CurrentYearTotal NUMERIC(18,2), @PriorYearTotal NUMERIC(18,2)
>
>/* Get Current Year's Total Sales */
>Select @CurrentYearTotal = SUM(SalesAmountUSDollars)
>From slsMain
>Where InvoiceDate >= '01/01/2004' and InvoiceDate <= '12/31/2004'
>
>
>/* Get Prior Year's Total Sales */
>Select @PriorYearTotal = SUM(SalesAmountUSDollars)
>From slsMain
>Where InvoiceDate >= '01/01/2003' and InvoiceDate <= '12/31/2003'
>
>
>/* Get Current Year Customer Totals and Join with Prior Year Customer Totals.
>    Add Current Total Sales and Prior Total Sales */
>Select CurrentSales.CustomerNumber, CurrentSales.CustomerName,
>	CurrentSales.CurrentSales, CurrentYearByMonth.CurrentYearMonthlySales,
>	@CurrentYearTotal AS CurrentTotalSales,
>	#TempPriorSales.PriorYearRank, #TempPriorSales.PriorSales,
>	#TempPriorSales.PriorYearMonthlySales,
>	@PriorYearTotal AS PriorTotalSales
>From
>   (
>	Select TOP 300 CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) CurrentSales
>	From slsMain
>	Where InvoiceDate >= '01/01/2004'
>	Group By CustomerNumber, CustomerName
>	Order By SUM(SalesAmountUSDollars) DESC
>   ) CurrentSales
>   LEFT OUTER JOIN
>   (
>	Select CustomerNumber, CustomerName, SUM(SalesAmountUSDollars) CurrentYearMonthlySales
>	From slsMain
>	Where InvoiceDate >= '09/01/2004'
>	Group By CustomerNumber, CustomerName
>   ) CurrentYearByMonth
>   ON CurrentSales.CustomerNumber = CurrentYearByMonth.CustomerNumber and
>	CurrentSales.CustomerName = CurrentYearByMonth.CustomerName
>   LEFT OUTER JOIN
>   	#TempPriorSales
>   ON CurrentSales.CustomerNumber = TempPriorSales.CustomerNumber and
>	CurrentSales.CustomerName = TempPriorSales.CustomerName
>Order By CurrentSales.CurrentSales DESC
>
>
>/* Drop TempPriorSales Table */
>DROP TABLE #TempPriorSales
>GO
>