Add column which identifies the frequency of repeat customers based on number of orders during 2007

Question

The column should contain: 'No Order' for count = 0 'One Time' for count = 1 'Regular' for count range of 2-5 'Often' for count range of 6-10 'Loyal' for count greater than 10

Below is the query, where I am not able to use other conditions like 'greater than, range'

SELECT c.CustomerID, c.TerritoryID,
COUNT(o.SalesOrderid) [Total Orders],
CASE COUNT(o.SalesOrderid)
WHEN 1 
THEN 'One Time' 
WHEN 0
THEN 'No Order'
ELSE 'None' 
END AS [Frequency]  
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
WHERE DATEPART(year, OrderDate) = 2007
GROUP BY c.TerritoryID, c.CustomerID;

Screenshot of my output

Answer 1

Instead of a simple CASE expression, you can use the searched format as described in the documentation like the below example. Also, I suggest you avoid applying the DATEPART function to the OrderDate in the WHERE and refactor like below so the expression is sargable, allowing efficient index use.

SELECT
      c.CustomerID
    , c.TerritoryID
    , COUNT(o.SalesOrderid) [Total Orders]
    , CASE 
        WHEN COUNT(o.SalesOrderid) = 0 THEN 'No Order'
        WHEN COUNT(o.SalesOrderid) = 1 THEN 'One Time' 
        WHEN COUNT(o.SalesOrderid) BETWEEN 2 AND 5 THEN 'Regular'
        WHEN COUNT(o.SalesOrderid) BETWEEN 6 AND 10
        THEN 'Often'
        ELSE 'Loyal' 
    END AS [Frequency]  
FROM Sales.Customer c
LEFT OUTER JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
WHERE OrderDate >= '20070101' AND OrderDate < '20080101'
GROUP BY c.TerritoryID, c.CustomerID;