Hi!

I wrote this sql-query script the best I could and I couldn't figure out a way to minimize the script. The script is way overkill. I'm using too many query for locations and I only need the 1st record result. I'm not proud of it.

I wonder is there a way to fetch the 1st record result and stop there instead of having to go through next query. I wonder is there a way to get the result using a few lines of code.

Thanks...

Code:
; WITH MyCte(SalePrice, RepairCost, PurchPrice, Mileage, NumOfDays, Region, City, State, ZipCode) AS 
 ( 
     SELECT 
         SalePrice, RepairCost, PurchPrice, Mileage, NumOfDays, Region, State, City, ZipCode 
     FROM tblSold 
     WHERE ((SoldDate >= CONVERT(DATETIME, @parmVehicleSoldDateBegin + ' 00:00:00.000')) AND (SoldDate <= CONVERT(DATETIME, @parmVehicleSoldDateEnd + ' 23:59:59.999'))) 
     AND Year = @parmVehicleYear 
     AND MakeID = @parmVehicleMake 
     AND ModelID = @parmVehicleModel 
     AND Style = @parmVehicleTrim 
 ) 
 
 SELECT TOP 1 
     CAST((CASE WHEN SalePrice1 IS NULL THEN 0 ELSE SalePrice1 END AS DECIMAL(10,2)) AS SalePrice 
     CAST((CASE WHEN RepairCost1 IS NULL THEN 0 ELSE RepairCost1 END) AS DECIMAL(10,2)) AS AvgRepair, 
     CAST((CASE WHEN Mileage1 IS NULL THEN 0 ELSE Mileage1 END) AS INT) AS AvgMileage, 
     CAST((CASE WHEN NumOfDays1 IS NULL THEN 0 ELSE NumOfDays1 END) AS INT) AS NumDays, 
     SortOrder 
 FROM 
 ( 
     --ZipCode Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         1 AS SortOrder 
     FROM MyCte 
     WHERE ZipCode = @parmVehicleZipcode 
     GROUP BY ZipCode 
         UNION 
     --ZipCode Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         2 AS SortOrder 
     FROM MyCte 
     WHERE ZipCode = @parmDealerZipcode 
     GROUP BY ZipCode 
         UNION 
     --City Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         3 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmVehicleState 
     AND City = @parmVehicleCity 
     GROUP BY City, State 
         UNION 
     --City Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         4 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmDealerState 
     AND City = @parmDealerCity 
     GROUP BY City, State 
         UNION 
     --State Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         5 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmVehicleState 
     GROUP BY State 
         UNION 
     --State Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         6 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmDealerState 
     GROUP BY State 
         UNION 
     --Region Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         7 AS SortOrder 
     FROM MyCte 
     WHERE Region = @parmVehicleRegion 
     GROUP BY Region 
         UNION 
     --Region Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
     8 AS SortOrder 
     FROM MyCte 
     WHERE Region = @parmDealerRegion 
     GROUP BY Region 
         UNION 
     --National Location-Search/Dealer's-Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         9 AS SortOrder 
     FROM MyCte 
 ) AS tmpTable1 
 ORDER BY SortOrder