Showing statement using case clause

• Peter Grins

  SSC Rookie

  Points: 30

  More actions

  July 30, 2017 at 11:08 am

  #338550

  Hi guys, I am new here and I am very interested in SQL (and learning).

  I have the following query and would like to show the Customer Type in a table as follow:

  CustomerID....Name............LastPayment..........CustomerType
  100.................Peter.............0    
  101.................Paul..............45000    
  102.................Amanda........35000    
  103.................Hannah.........3000    
  104.................Unig..............45000    
  105.................Alma.............12    

  CustomerType is based on the # of Active Subscription and if the LastPayment is greater than 0

  The Best CustomerType = Loyal
  The Second CustomerType = Discount
  The 3rd CustomerType = Impulse
  The 4th CustomerType = Wandering

  Active Subscription: >= Current Timespan
  Inactive Subscription: < Current Timespan

  1. In order to do it, we will have to show the number of Subscription by each Customers (Active and Inative) in a table then...
  2. I think we will have to use the CASE clause (correct me if I am wrong).

  Can anyone show me how to write this query?

  Thanks
  

  Create table #Subscription
(
    CustID INT,
    StartDt Date,
    EndDt Date
)

  Insert into #Subscription

  Select 100, '2013-09-30','2013-12-31'
Union all
Select 100, '2014-02-28','2014-12-31'     --There is a gap of almost 2 months from the last Subscription 
Union all
Select 100, '2015-01-01','2017-12-31'      --No Gap
Union all
Select 100, '2016-06-30','2017-12-31'     --No Gap
Union all
Select 101, '2014-05-30','2014-12-31'
Union all
Select 101, '2015-09-30','2015-12-31'     --Gap of almost 9 months from the last Subscription and so on so on
Union all
Select 102, '2017-01-30','2017-07-31'     --No Gap
Union all
Select 102, '2017-05-30','2017-12-31'
Union all
Select 103, '2013-08-01','2015-12-31'
Union all
Select 104, '2016-12-31','2017-12-31'     
Union all
Select 104, '2017-06-30','2018-06-30'     
Union all
Select 105, '2014-05-30','2014-12-31'
Union all
Select 105, '2014-05-30','2014-12-31'
Union all
Select 105, '2014-05-30','2014-12-31'
Union all
Select 106, '2016-07-30','2017-07-26'
Union all
Select 103, '2017-04-30','2017-08-30'
Union all
Select 103, '2017-07-30','2017-05-30'
Union all
Select 103, '2017-12-30','2018-12-30'

  Create Table #Customer
(   CustomerID INT IDENTITY (100,1)
  ,  Name NVarchar(40)
  ,  LastPayment Money
  ,  CustomerType NVarchar(10)
)

  INSERT INTO #Customer
Select 'Peter','0',''
Union all
Select 'Peter','45000',''
Union all
Select 'Amanda','35000',''
Union all
Select 'Hannah','3000',''
Union all
Select 'Unig','45000',''
Union all
Select 'Alma','12',''

• Thom A

  SSC Guru

  Points: 99137

  More actions

  July 31, 2017 at 5:09 am

  #1953342

  Great that you provided sample DDL and DLM, however, without an expected output or your logic, we can't give you an answer. Also, you have CustomerType as a column, but I assume that the customer type could change. I'd personally suggest you calculate the value each time you do the query, rather than storing it. If you need it in a "selectable" format you could use a VIEW. In my answer below, note i drop the CustomerType Column.

  I've included a guess answer, however, if this doesn't suit your needs, post your expected output and desired logic so that we can provide a more complete answer.

  ALTER TABLE #Customer DROP COLUMN CustomerType;
GO

  SELECT C.CustomerID, C.Name, C.LastPayment,
       CASE WHEN C.LastPayment <= 0 OR S.Subscriptions = 0 THEN 'Wandering'
            WHEN S.Subscriptions = 1 THEN 'Impulse'
            WHEN S.Subscriptions BETWEEN 2 AND 4 THEN 'Discount'
            WHEN S.Subscriptions >= 5 THEN 'Loyal' END AS CustomerType
FROM #Customer C
    OUTER APPLY (SELECT COUNT(*) AS Subscriptions
                  FROM #Subscription oa
                  WHERE oa.CustID = C.CustomerID
                   AND oa.EndDt >= GETDATE()) S;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk