Land n Sea - eBay / Magento SQL Server Query

LnS_Product_Query.sql

USE companydb
DECLARE @companyname_active TABLE
    (
     [Custom Label] VARCHAR(75)
	,[Purchases] INT
	,[Category Number] INT
	,[Price] DECIMAL(6,2)
	,[Item Title] VARCHAR(90)

    )

INSERT INTO @companyname_active
    (     
     [Custom Label]
	,[Purchases]
	,[Category Number]
	,[Price]
	,[Item Title]
    )


SELECT
     SUBSTRING([Custom Label], CHARINDEX('-', [Custom Label])+1, len([Custom Label]))
	,MAX([Purchases])
	,MAX([Category Number])
	,MAX([Price])
	,MAX([Item Title])
FROM companyn_Active_Listings
GROUP BY SUBSTRING([Custom Label], CHARINDEX('-', [Custom Label])+1, len([Custom Label]));

USE companydb
DECLARE @margin FLOAT
SET @margin =18

SELECT
       
	   C.[Item Title]
	  ,CASE WHEN a.[LNS Part#] IS NULL THEN a.[Vendor Part#] ELSE a.[LNS Part#] END AS [SKU]
	  ,SUBSTRING(a.[LNS Part#],CHARINDEX('-',a.[LNS Part#])+1,LEN(a.[LNS Part#])) AS [MPN]
      ,a.[MSRP] AS [MSRP]
      --,dbo.UPS_Ground_Cost(a.Weight) AS Ship_Cost
      ,b.DealerCost AS Item_Cost
	  ,[Buy It Now Price] = CAST(((b.DealerCost+dbo.UPS_Ground_Cost(a.Weight))/(1-(@margin/100)))*1.12+.35 AS decimal(6,2))

	  ,CASE WHEN ISNUMERIC(a.UPC) = 1 AND LEN(a.[UPC]) = 12 THEN a.[UPC] 
	        WHEN ISNUMERIC(a.UPC) = 1 AND LEN(a.[UPC]) = 11 THEN '0'+a.[UPC] 
	        ELSE '' END AS [UPC]

	  ,c.[Category Number] AS eBayCategoryID
	  ,6 AS StoreCategoryID
	  
      ,TRY_CAST(a.[Weight] AS decimal(4,2)) AS [Weight]
      ,TRY_CAST(a.[Length] AS decimal(4,2)) AS [Length]
      ,TRY_CAST(a.[Width] AS decimal(4,2)) AS [Width]
      ,TRY_CAST(a.[Height] AS decimal(4,2)) AS [Height]
	  ,'C:\Users\clint\Desktop\vendorname'+d.RelativePath+d.Image1 AS Image1
	  ,CASE WHEN d.Image2 != ''
	   THEN 'C:\Users\clint\Desktop\vendorname'+d.RelativePath+d.Image2
	   ELSE '' END AS Image2
	  ,CASE WHEN d.Image2 != ''
	   THEN 'C:\Users\clint\Desktop\vendorname'+d.RelativePath+d.Image3
	   ELSE '' END AS Image3
	  ,REPLACE(REPLACE(REPLACE(
	  D.WebLongDescription+SPACE(1)+d.WebFeatures,'&#44',''),
	  '"',''),
	  '<sup>&reg;</sup>','') AS ProdLong_Desc
	  ,d.WebShortDescription AS Short_Description
	  ,b.TotalQuantity AS QTY

  

	  --,RTRIM(LTRIM(b.[vendornameSKU])) -- Verification Column to confirm a = b

  FROM [dbo].[vendorname_Catalog_Staging] AS a
  
  LEFT JOIN [vendorname_Master] AS b
  ON RTRIM(LTRIM(a.[LNS Part#])) = RTRIM(LTRIM(b.[vendornameSKU]))

  LEFT JOIN @companyname_active AS C
  ON a.[LNS Part#] = c.[Custom Label]

  LEFT JOIN [LnS_Web_Content] AS d
  ON a.[LNS Part#] = d.[LNSPartNo]
  

  WHERE 
      a.[Ship Restr] != 'Y' -- Exclude all Listings that have Shipping Restrictions of any type.
      AND c.[Custom Label] IS NOT NULL
	  AND c.Purchases >= 2
	  AND d.[LNSPartNo] IS NOT NULL
 

 -- Title Case in Item Title
 -- Improve words used in Ttle