Is a Temp Table the best solution?

Question

I'm building a stored proc that pulls a lot of different data from several different tables, and I need a way to pull sales information from a sales table and then perform various summations on that sales data. In the example below I accomplish this using a temp table, but someone suggested there's probably a better way. Is there a more efficient way to accomplish what I'm doing here?

 SELECT * INTO #TempSales FROM [Sales] WHERE ClientId = @ClientId
    SELECT 
        [Customer].[CustomerID],
        [Customer].[AccountBalAmountOpen],
        [Customer].[AccountAgeAmountDays0],
        [Customer].[AccountAgeAmountDays30],
        [Customer].[AccountAgeAmountDays60],
        [Customer].[AccountAgeAmountDays90],
        [Customer].[AccountAgeAmountDaysOver90],
        (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 385) AS ServiceLifeTimeSales,
        (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 385 AND MONTH(SaleDate) = MONTH(GETDATE()) AND YEAR(SaleDate) = YEAR(GETDATE())) AS ServiceMonthToDateSales,
        (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 385 AND YEAR(SaleDate) = YEAR(GETDATE())) AS ServiceYearToDateSales,
        (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 385 AND YEAR(SaleDate) = (YEAR(GETDATE()) - 1)) AS ServicePreviousYearSales,
    (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 460) AS PartsLifeTimeSales,
        (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 460 AND MONTH(SaleDate) = MONTH(GETDATE()) AND YEAR(SaleDate) = YEAR(GETDATE())) AS PartsMonthToDateSales,
        (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 460 AND YEAR(SaleDate) = YEAR(GETDATE())) AS PartsYearToDateSales,
        (SELECT SUM(SalesAmount) FROM #TempSales WHERE CustomerId = [Customer].[CustomerID] AND Origin = 460 AND YEAR(SaleDate) = (YEAR(GETDATE()) - 1)) AS PartsPreviousYearSales,
        [Orders].[CustomerId] AS ParentCustomerId,
        [Orders].[OrderId],
        [Orders].[OrderStatus],
        [Orders].[UnitId],
        [Orders].[FleetId],
        [Orders].[CreatedDate] AS OrderCreatedDate,
        [Orders].[OrderType],
        [OrderParts].[OrderId] AS ParentOrderId,
        [OrderParts].[PartId],
        [OrderParts].[PartDescription],
        [OrderParts].[QuantityShip],
        [OrderParts].[QuantityBackOrder],
        [OrderParts].[CreatedDate] AS PartCreatedDate
      FROM [Customer]
      LEFT JOIN [Orders]
      ON [Orders].[CustomerId] = [Customer].[CustomerID]
      LEFT JOIN [OrderParts]
      ON [OrderParts].[OrderId] = [Orders].[OrderId]
      WHERE [Customer].[ClientID] = @ClientId
DROP TABLE #TempSales

Answer 1

Although temporary tables can be useful to store intermediate results of complex queries that are going to be reused in other queries, I don't think that you need it for your calculations as you are creating the temporary table as this:

SELECT * INTO #TempSales FROM [Sales] WHERE ClientId = @ClientId

You are not joining the table with anything nor making calculations or using aggregate functions, so if the table is properly indexed (at least ClientId, CustomerId, Origin and SalesDate), your query should perform fine or even faster, because tables created using SELECT INTO doesn't have any index unless you manually add them.

But probably you are experiencing a speed improvement using the temporary table because you have conditions like this:

AND YEAR(SaleDate) = YEAR(GETDATE())
AND MONTH(SaleDate) = MONTH(GETDATE()) AND YEAR(SaleDate) = YEAR(GETDATE())

which make the query non-sargable as you are enclosing the filter column in a function. Due to this the query optimizer can't properly use any existing index on SaleDate, and since the temporal table has fewer rows it can take less time perform a full scan on it than on the Sales table.

You can fix that using a condition that doesn't apply any function to the filter column (although you can use functions on constant values):

declare @thisYearStart as datetime, @nextYearStart as datetime,
  @thisMonthStart as datetime, @nextMonthStart as datetime

set @thisYearStart=DATEFROMPARTS(YEAR(GETDATE()),1,1)
set @nextYearStart=DATEADD(year,1,@thisYearStart)
set @thisMonthStart=DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)
set @nextMonthStart=DATEADD(month,1,@thisMonthStart)

or if you are using a version of SQL older than SQL Server 2012:

set @thisYearStart=CAST(CAST(YEAR(GETDATE()) as char(4))+'0101' AS datetime) -- ISO format
set @nextYearStart=DATEADD(year,1,@thisYearStart)
set @thisMonthStart=DATEADD(month,MONTH(GETDATE())-1,@thisYearStart)
set @nextMonthStart=DATEADD(month,1,@thisMonthStart)

and then just use:

AND SaleDate>=@thisYearStart AND SaleDate<@nextYearStart
AND SaleDate>=@thisMonthStart AND SaleDate<@nextMonthStart

Note that you don't need to use the variables, you could use the DATEFROMPARTS(...) directly on the WHERE condition but since you are already creating a stored procedure using them they will make the query more readable.