Select with in a Select

1

I want to Go through a customer file and get customer info but then also be able to display that customers sales for this year and last year by going through the sales file. I thought the best way to get current and prior year data would be a select in a select. The code is below. If I am totally wrong on the approach or just need different code either way will help me get better.

SELECT CustomerID,BillToCustomerId, CustName, BillToCustName, ASR_Name,
(SELECT TOP (1) SUM(Sales)
FROM dbo.Sales_Detail_wBillTo
WHERE [Inv-Date]>= @StartDate AND [Inv-Date]<= @EndDate and DIM200 = CustomerID and dbo.CustBillToCustXRef.BillToCustomerId = dbo.Sales_Detail_wBillTo.BillToCustomerId and DIM202 in (@CustClass) and DIM000 in (@ASR) and DIM900 in (@SkuType)
GROUP BY DIM200, dbo.Sales_Detail_wBillTo.BillToCustomerId) AS CurrentUnits,
(SELECT TOP (1) SUM(Sales)
FROM dbo.Sales_Detail_wBillTo
WHERE [Inv-Date]>= DATEADD(year, - 1, @StartDate) AND [Inv-Date]<= DATEADD(year, - 1, @EndDate) and DIM200 = CustomerID and dbo.CustBillToCustXRef.BillToCustomerId = dbo.Sales_Detail_wBillTo.BillToCustomerId and DIM202 in (@CustClass) and DIM000 in (@ASR) and DIM900 in (@SkuType)
GROUP BY DIM200, dbo.Sales_Detail_wBillTo.BillToCustomerId) AS PriorUnits
FROM CustBillToCustXRef

2

There's a couple of things you might want to change:

  1. Alias your tables. (so you don't need things like dbo.CustBillToCustXRef.BillToCustomerId = dbo.Sales_Detail_wBillTo.BillToCustomerId)
  2. use two-part naming for all columns (using the aliases from step 1)

together, these will make it easier for the next dev to understand your work.

  1. you have SELECT TOP (1) but no ORDER BY clause. This means that the row actually selected is not deterministic. Even if you really think only one row should be returned, putting in the ORDER BY is good practice.

  2. Your nested selects might turn out to be a performance issue, unless the tables in the subqueries are indexed on the columns you want to match on. Imagine that, for every row processed in the outer query, all the rows in the second table get processed (due to missing indexes).

Something like this might give you the same results with less code:

WITH BillTo AS
(
	SELECT SALES, [Inv-Date], DIM200, BillToCustomerId, DIM202, DIM000, DIM900
	FROM dbo.Sales_Detail_wBillTo 
	WHERE DIM202 IN (@CustClass)
      AND DIM000 IN (@ASR)
      AND DIM900 IN (@SkuTypccc

)
    SELECT XRef.CustomerID
        , XRef.BillToCustomerId
        , XRef.CustName
        , XRef.BillToCustName
        , XRef.ASR_Name
    	, SUM(B1.Sales) AS CurrentUnits
    	, SUM(B2.Sales) AS PriorUnits
    FROM CustBillToCustXRef XRef
    LEFT JOIN BillTo B1
      ON XREF.BillToCustomerId = B1.BillToCustomerId	
    LEFT JOIN BillTo B2
      ON XREF.BillToCustomerId = B2.BillToCustomerId  
    WHERE B1.[Inv-Date] >= @StartDate
      AND B1.[Inv-Date] <= @EndDate
      AND B2.[Inv-Date] >= DATEADD(year, - 1, @StartDate)
      AND B2.[Inv-Date] <= DATEADD(year, - 1, @EndDate)
      
     GROUP BY XRef.CustomerID
        , XRef.BillToCustomerId
        , XRef.CustName
        , XRef.BillToCustName
        , XRef.ASR_Name