July 30, 2017 at 11:08 am
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',''
July 31, 2017 at 5:09 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply