Solved

Number of times customer changed subscription

Posted on 2014-03-09
10
204 Views
Last Modified: 2014-03-18
Hello,

Table Structure
Please see the above table structure.

Basically I want to group by Customer Id and Year, and then get the count of number of times a customer changed from Paper to Online Subscription, as well as Online to Paper Subscription.

How do I achieve this?

Thanks!
0
Comment
Question by:sath350163
  • 4
  • 3
  • 3
10 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39916711
Hi,

My thinking is that you want a self-join from the first row to the next row (and so on) so that you can see what the change is.

Given that there is no specific linking between rows, I'd look at a CTE that ranks the rows for each customer over date.

Then the join is
from cte_name as a -- row
left outer join cte_name as b -- next row
    on a.CustomerID = b.CustomerID
    and a.Rank + 1 = b.rank

HTH
  David
0
 
LVL 7

Expert Comment

by:Anoo S Pillai
ID: 39916739
One way to look into the issue:-

Identify the count of Online as well as Paper types for each customer within each year. ( Assuming that type will be changed during consecutive entries ).

This can be identified by using something like the following.

Select	 CustomerID,  YEAR(SubscriptionChangeDate) , 
		 count ( Case when SubscriptionType = 'Online' then 1 else null end  ) as Online, 
		 count ( Case when SubscriptionType = 'Paper' then 1 else null end  ) as Paper
FROM CustomerTab
group by CustomerID , YEAR(SubscriptionChangeDate) , SubscriptionType 

Open in new window


Second part of the issue is, reduce the count by one for the first type of transaction, if it is a carry over from previous year. That should not be difficult. If you need support on that please post table creation script and sample data insert script.


OR
if you are using SQL 2012, OVER() clause with LEAD and LAG would be make the solution easier.
0
 

Author Comment

by:sath350163
ID: 39916759
Thanks for the responses.

Below is the table script and insert data sql statements.
Can you please show me how to do the second part?

CREATE TABLE dbo.Customer 
(CustomerId				INT			NOT NULL,
 SubscriptionChangeDate DATETIME	NOT NULL,
 SubscriptionType		VARCHAR(20) NOT NULL
); 
 
 
INSERT INTO dbo.Customer 
VALUES
(1, '01-JAN-2012','Online'),
(1, '02-MAR-2012','Paper'),
(1, '10-Nov-2012','Online'),

(1, '04-Mar-2013','Paper'),
(1, '07-Aug-2013','Online'),

(2, '01-JAN-2014','Online'),

(3, '01-JUN-2013','Paper'),
(3, '01-SEP-2013','Online'),
(3, '05-NOv-2013','Paper'),
(3, '28-Dec-2013','Online');

Open in new window




The logic that you have shown seems to count the number of records with SubscriptionType = 'Online' and 'Paper' for each CustomerId within each Year.

But my requirement is to capture the count of transition from Online to Paper (and) Paper to Online for each CustomerId within each year.

I.e, For example
For CustomerId = 3 within 2013, since that customer has changed from
- PAPER to ONLINE one time, and then
- ONLINE to PAPER another time, and then
- PAPER to ONLINE once again,
the output should show 2 under column "PaperToOnline" and 1 under column "OnlineToPaper" (as shown in the picture).



Thanks!
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 35

Expert Comment

by:David Todd
ID: 39916765
Hi,

This should help.

Regards
  David

with customer_ordered ( CustomerID, SubscriptionType, RowNo )
as (
	select 
		c.CustomerId
		, c.SubscriptionType
		, rank() over( partition by c.CustomerID order by c.SubscriptionChangeDate ) as RowNo
	from dbo.Customer c
)

select a.CustomerID, a.SubscriptionType, b.SubscriptionType
from customer_ordered a
left outer join customer_ordered b
	on b.CustomerID = a.CustomerID
	and b.RowNo = a.RowNo + 1
where
	b.CustomerID is not null
;

Open in new window

0
 
LVL 7

Expert Comment

by:Anoo S Pillai
ID: 39916936
Three quick questions -
Which version of SQL server you are using ?
Is the query from David resolve your issue ?
Can I assume that type will be changed during consecutive entries ?
0
 
LVL 7

Expert Comment

by:Anoo S Pillai
ID: 39917088
Posting a draft query, May be helpful to start.



select CustomerId , 
       Yr , 
       Sum ( Online ) PaperToOnline , 
       Sum(Paper) OnlineToPaper
from 
(   Select	 c.CustomerID,  YEAR(SubscriptionChangeDate) Yr , 
			 count(Case when SubscriptionType = 'Online' and SubscriptionChangeDate <> StartDate then 1 
						else null end  )  as Online, 
			 count(Case when SubscriptionType = 'Paper' and SubscriptionChangeDate <> StartDate then 1 
						else null end  )   as Paper
	FROM	Customer  c  
	inner join ( /*Remove starting transaction type */ 
				 select CustomerID,
				        min(SubscriptionChangeDate) StartDate 
				 from Customer 
				 group by CustomerID )FirstTran
	on  c.CustomerID = FirstTran.CustomerID 
	group by c.CustomerID , 
			 YEAR(SubscriptionChangeDate) , 
			 SubscriptionType  
) AS FinTab
Group By CustomerId , Yr 
order by CustomerId

Open in new window

0
 

Author Comment

by:sath350163
ID: 39928420
Sorry for the delay in response.

Below is my answer to the 3 question:

Question 1:
Which version of SQL server you are using ?  
SQL SERVER 2008 R2


Question 2:
Is the query from David resolve your issue ?
It works, but does not cover the case when the Subscription type is the same for a customer across records with diff SubscriptionChangeDate.

Eg: please insert the following values to the table mentioned in my earlier post.

INSERT INTO dbo.Customer 
VALUES
(4, '01-JUN-2013','Paper'),
(4, '01-SEP-2013','Paper'),
(4, '05-NOv-2013','Paper'),
(4, '28-Dec-2013','Paper');

(5, '01-JUN-2013','Paper'),
(5, '01-SEP-2013','Paper'),
(5, '05-NOv-2013','Online'),
(5, '28-Dec-2013','Online');

Open in new window


The above CTE sql, returns the following for Customer Id = 4
CustomerId      Yr          PaperToOnline      OnlineToPaper
4                     2013          0                               3
5                     2013          2                               1


But it should actually return:
CustomerId      Yr          PaperToOnline      OnlineToPaper
4                     2013          0                               0
5                     2013          1                               0



as all the subscription change for CustomerId = 4 is between the same Subscription type (I.e, PAPER to PAPER), and for CustomerId = 5, there is only subscription change that is between different Subscription types (Paper to Online).


Question 3:
Can I assume that type will be changed during consecutive entries ?
Not necessary. The consecutive entries may have the same type (for eg, if they changed from ONLINE to subscription to ONLINE Subscription) or different type  (For eg, If they changed from ONLINE to PAPER/PAPER to ONLINE)
0
 
LVL 7

Assisted Solution

by:Anoo S Pillai
Anoo S Pillai earned 250 total points
ID: 39929572
Probably the following code may be helpful. Try and revert ( without delay :)  )

;with CTE_Customer 
as ( select c.CustomerId, c.SubscriptionType, c.SubscriptionChangeDate, year(SubscriptionChangeDate) Yr,
            rank() over( partition by c.CustomerID ,  
                                      year(SubscriptionChangeDate) 
                         order by c.SubscriptionChangeDate ) as RowNum 
	 from dbo.Customer c ), 
CTE_SubChanges 
as ( select	c1.CustomerID, c1.SubscriptionType SourceSubType, c1.Yr, 
			c2.SubscriptionType TargetSubType,c2.SubscriptionChangeDate, 
			case when c1.SubscriptionType <> c2.SubscriptionType then 1 else 0 end as ChangeCount
	 from	CTE_Customer c1
	 left join CTE_Customer c2
	 on		c1.CustomerID = c2.CustomerID
	 and	c1.RowNum + 1 = c2.RowNum )
select	CustomerID,  
		Yr , 
		SUM ( Case when TargetSubType = 'Online' then ChangeCount else 0 end  ) as PaperToOnline, 
		SUM ( Case when TargetSubType = 'Paper' then ChangeCount else 0 end  )  as OnlineToPaper
from  CTE_SubChanges FT
group by FT.CustomerID , Yr
order by FT.CustomerID , Yr

Open in new window

0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 39930369
Hi,

with customer_ordered ( CustomerID, SubscriptionType, SubscriptionChangeDate, RowNo )
as (
	select 
		c.CustomerId
		, c.SubscriptionType
		, c.SubscriptionChangeDate
		, rank() over( partition by c.CustomerID order by c.SubscriptionChangeDate ) as RowNo
	from dbo.Customer c
)

select
	z.CustomerID
	, year( z.SubscriptionChangeDate ) as yr
	, isnull( sum( z.PaperToOnline ), 0 ) as TotalPaperToOnline
	, isnull( sum( z.OnlineToPaper ), 0 ) as TotalOnlineToPaper
from (
	select 
		a.CustomerID
		, b.SubscriptionChangeDate
		, a.SubscriptionType as OldType
		, b.SubscriptionType as NewType
		, case when a.SubscriptionType = 'Paper' and b.SubscriptionType = 'Online' then 1 end as PaperToOnline
		, case when a.SubscriptionType = 'Online' and b.SubscriptionType = 'Paper' then 1 end as OnlineToPaper
	from customer_ordered a
	left outer join customer_ordered b
		on b.CustomerID = a.CustomerID
		and b.RowNo = a.RowNo + 1
	where
		b.CustomerID is not null
	) z
group by
	z.CustomerID
	, year( z.SubscriptionChangeDate )
order by
	z.CustomerID
	, year( z.SubscriptionChangeDate )
;

Open in new window


HTH
  David
0
 

Author Comment

by:sath350163
ID: 39938399
Both the solutions work prefectly.

    Thanks you David and Anoo!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question