• Category 1: ProductCodes that do not have any dashes or letters.


• Category 2: ProductsCodes that have dashes in position 5 (e.g. XXXX-XX) and may contain letters.


• Category 3: ProductCodes that start with LIT- (e.g. LIT-0001)


• Category 4: All ProductCodes that do not fall in three categories above.

SELECT ItemCode
      ,ItemName
      ,LEN(ItemName) AS [Length]
      ,(CASE
	    WHEN PATINDEX('%-%', ItemName) = '5' THEN 'Prosthetic'
	    WHEN PATINDEX('%-%', ItemName) = '0' AND LEN(ItemName) <> 4 THEN 'Implant'
	    WHEN ItemName LIKE 'LIT-%' THEN 'Catalogs'
	    WHEN LEN(ItemName) = 4 AND ItemName LIKE '1%' OR ItemName LIKE '2%' THEN 'SurgicalMotor'
	  ELSE 'Surgical'
	  END) AS Category
  FROM InventoryItem

  ORDER BY Category ASC, ItemName

SELECT 
  ItemCode,
  Prosthetic = SUM(CASE WHEN PATINDEX('%-%', ItemName) = '5' THEN 1 ELSE 0 END),
  Implant = SUM(CASE WHEN PATINDEX('%-%', ItemName) = '0' AND LEN(ItemName) <> 4 THEN 1 ELSE 0 END),
  Catalogs = SUM(CASE  WHEN ItemName LIKE 'LIT-%' THEN 1 ELSE 0 END),
  SurgicalMotor = SUM(CASE WHEN LEN(ItemName) = 4 AND ItemName LIKE '1%' OR ItemName LIKE '2%' THEN 1 ELSE 0 END),
  Surgical = SUM(CASE WHEN ... THEN 1 ELSE 0 END)
FROM InventoryItem
GROUP BY ItemCode

SUM(CASE WHEN LEN(ItemName) = 6 AND ISNUMERIC(LEFT(ItemName, 6)) = 1 THEN 1 ELSE 0 END)

quote:

---

Originally posted by fawadafr

Hello Lumbago,
Once again, thank you very much for taking your time helping me with this project.
I executed your script and was able to categorize most of our products. Here is the link if you would like to see the results:

[url]http://www.implantdirect.com/Ahmad/msdn/xls/Results.xlsx[/url]

I noticed your script for the second category did not return any results.

SUM(CASE WHEN LEN(ItemName) = 6 AND ISNUMERIC(LEFT(ItemName, 6)) = 1 THEN 1 ELSE 0 END)

I check the property of ItemName in SQL server and discovered it is nvarchar(100). I believe since you are using ISNUMERIC() function, it doesn't return any value. How can we fix this?

There are still some products that are shown in two different categories. A product can only have one category.

Thank you,


--
Fawad Rashidi

---

SELECT  Customer.CustomerCode
		,Customer.CustomerName
		,CustomerSalesOrder.SalesOrderCode AS Expr1
		,CustomerSalesOrder.SalesOrderDate
		,CustomerSalesOrder.Total
		,CustomerSalesOrderDetail.ItemCode
		,InventoryItem.ItemName
FROM	CustomerSalesOrderDetail 
		INNER JOIN CustomerSalesOrder
		ON CustomerSalesOrderDetail.SalesOrderCode = CustomerSalesOrder.SalesOrderCode
		INNER JOIN InventoryItem
		ON CustomerSalesOrderDetail.ParentItemCode = InventoryItem.ItemCode
		INNER JOIN Customer
		ON CustomerSalesOrder.BillToCode = Customer.CustomerCode