Cacher is the code snippet organizer for pro developers

---First, add all warehouses you want below
IF OBJECT_ID('tempdb..#WHSE') IS NOT NULL DROP TABLE #WHSE
SELECT DISTINCT a.WarehouseID
INTO #WHSE
FROM _Boris..VW_Warehouse a
WHERE a.WarehouseID = 3

----Next we want to compile active, relevant customers
IF OBJECT_ID('tempdb..#CUSTOMERS') IS NOT NULL DROP TABLE #CUSTOMERS
SELECT a.CustomerID
	,a.ShopName
	,a.Contact
	,a.Address
	,a.City
	,a.State
	,a.Zip
	,a.WarehouseID
	,a.CompanyID
	,a.Route
	,a.CustomerStatusCode
	,a.CustomerTypeSegment
	,a.IsECOM
	,a.IsIntegrated
	,a.PrimaryPurchaseMethod
	,a.ShopManagementSystem
	,a.OverallStatus
	,a.Sales30
	,a.Sales60
	,a.Sales90
	,a.Sales180
	,a.Sales360
	,a.ElectronicQuote30
	,a.ElectronicQuote60
	,a.ElectronicQuote90
	,a.ElectronicQuote180
	,a.ElectronicQuote360
INTO #CUSTOMERS
FROM _rcope.dbo.Customers a
	JOIN #WHSE b
		ON a.WarehouseID = b.WarehouseID
WHERE a.IsActive = 1 ---only looking at active customers
AND (IsECOM = 1
OR IsIntegrated = 1) ---only looking at integrated or customers with ecom accts
AND a.ShopName NOT LIKE '%insurance%'
AND a.ShopName NOT LIKE '%test%'

---Next we want to look at their primary avenue for lookups
IF OBJECT_ID('tempdb..#LOOKUPS') IS NOT NULL DROP TABLE #LOOKUPS
SELECT a.PID
	,LTRIM(RTRIM(a.quoteuser)) as 'QuoteUser'
	,COUNT(*) as 'LUCount'
INTO #LOOKUPS
FROM dw.dbo.LookupSummary a
	JOIN #CUSTOMERS b
		ON a.pid = b.CustomerID
WHERE a.lookupDate >= DATEADD(DAY,-360,CONVERT(DATE,GETDATE()))
AND (LTRIM(RTRIM(a.quoteuser)) LIKE '%ecm%'
OR LTRIM(RTRIM(a.quoteuser)) LIKE '%ecom%')
GROUP BY a.pid
	,LTRIM(RTRIM(a.quoteuser))

IF OBJECT_ID('tempdb..#USER') IS NOT NULL DROP TABLE #USER
SELECT a.PID
	,a.QuoteUser
	,ROW_NUMBER() OVER(PARTITION BY a.PID ORDER BY LuCount DESC) as 'Max'
INTO #USER
FROM #LOOKUPS a

DELETE FROM #USER
WHERE [MAX] <> 1 

SELECT a.CustomerID
	,a.ShopName
	,a.Contact
	,a.Address
	,a.City
	,a.State
	,a.Zip
	,a.WarehouseID
	,a.CompanyID
	,a.Route
	,a.CustomerStatusCode
	,a.CustomerTypeSegment
	,a.IsECOM
	,a.IsIntegrated
	,ISNULL(b.QuoteUser,'None') as 'ModeQuoteUser'
	,a.PrimaryPurchaseMethod
	,a.ShopManagementSystem
	,a.OverallStatus
	,a.ElectronicQuote360/NULLIF(a.Sales360,0) as 'SalesPerLU'
	,a.Sales30
	,a.Sales60
	,a.Sales90
	,a.Sales180
	,a.Sales360
	,a.ElectronicQuote30
	,a.ElectronicQuote60
	,a.ElectronicQuote90
	,a.ElectronicQuote180
	,a.ElectronicQuote360
FROM #CUSTOMERS a
	LEFT JOIN #USER b
		ON a.CustomerID = b.pid
WHERE a.ElectronicQuote360 > 0