I have these table:
create table Customers ( Id bigint identity(1,1) primary key not null, Name varchar(100) not null ) create table CustomerActions ( Id bigint identity(1,1) primary key not null, [Date] datetime not null, CustomerId bigint not null [ActionId] bigint not null ) create table Actions ( Id bigint identity(1,1) primary key not null, Name varchar(100) not null )
and I have this view:
create view CustomerActionsView as select CustomerActions.*, Customers.Name as CustomerName, Action.Name as ActionName from CustomerActions inner join Actions on CustomerActions.ActionId = Actions.Id inner join Customers on CustomerActions.CustomerId = Customers.Id
I have two actions:
So basically this is a simple design to store when a customer subscribes, and when he cancells.
A sample data might look like this:
[Date]-CustomerName-Action "2018-04-24 17:44:20.000"-Rose-Subscribed "2019-01-11 10:37:45.000"-Rose-Cancelled
Now I'm stuck at finding customers who have subscribed twice (or more than once). That is, they've subscribed when they were already subscribed. Or they've cancelled when they were already cancelled.
In other words, I'm trying to find consecutive actions, ordered by [Date] descending.
I've tried this:
select * from ( select [Date], CustomerName, ActionName, row_number() over (partition by CustomerName, ActionName order by [Date] desc) as RowNumber from CustomerActionsView ) as Temp where RowNumber > 1
If I exclude
row_number() won't be useful. If I include
ActionName then it preceeds
I'm stuck at this point. How can I get repetitive consecutive actions?
Update: SQL Server version using
select @@VERSION returns this value:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)