Sequenced incremetn based on previous row change

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sequenced incremetn based on previous row change

Author

Topic

Kman287
Starting Member

5 Posts

Posted - 2014-07-06 : 14:33:11

Hi all,

I am pretty new to SQL and facing difficulties with a current problem.

I have a list of customers and a sequence of events they have triggered . I know the sequence in which these events have been triggered and only want to increment a value when a new event is triggered (value to remain the same if the event is the same). I have come close to a solution with Dense_rank but the problem here is that the ranking doesn't reset if the same even previously triggered is triggered a bit later in the sequence. see below an example of current results and expected results:

Customer ID,Sequence ID,event,current result,expected result
1,1,A,4,1
1,2,A,4,1
1,3,B,3,2
1,4,C,2,3
1,5,A,4,4
1,6,A,4,4
1,7,E,1,5
1,8,D,5,6
2,1,B,3,1
2,2,C,2,2
2,3,C,2,2

I hope someone can shed some lights.

Thanks
Kman287

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 15:29:16

Which version of SQL Server?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Kman287
Starting Member

5 Posts

Posted - 2014-07-06 : 15:31:13

I am using SQL SERVER 2012.

Thanks,
Kman287

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 15:49:15

[code]DECLARE @Sample TABLE
(
CustomerID TINYINT NOT NULL,
SequenceID TINYINT NOT NULL,
[Event] CHAR(1) NOT NULL,
CurrentResult TINYINT NOT NULL,
ExpectedResult TINYINT NOT NULL
);

INSERT @Sample
(
CustomerID,
SequenceID,
[Event],
CurrentResult,
ExpectedResult
)
VALUES (1, 1, 'A', 4, 1),
(1, 2, 'A', 4, 1),
(1, 3, 'B', 3, 2),
(1, 4, 'C', 2, 3),
(1, 5, 'A', 4, 4),
(1, 6, 'A', 4, 4),
(1, 7, 'E', 1, 5),
(1, 8, 'D', 5, 6),
(2, 1, 'B', 3, 1),
(2, 2, 'C', 2, 2),
(2, 3, 'C', 2, 2);

-- SwePeso
SELECT s.CustomerID,
s.SequenceID,
s.[Event],
s.CurrentResult,
s.ExpectedResult,
DENSE_RANK() OVER (PARTITION BY s.CustomerID ORDER BY s.SequenceID - CASE WHEN s.[Event] = y.[Event] THEN 0 ELSE 1 END) AS SwePeso
FROM @Sample AS s
LEFT JOIN @Sample AS y ON y.CustomerID = s.CustomerID
AND y.SequenceID = s.SequenceID + 1
ORDER BY s.CustomerID,
s.SequenceID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 15:54:45

[code]-- SwePeso (2012 solution)
WITH cteSource
AS (
SELECT CustomerID,
SequenceID,
[Event],
CurrentResult,
ExpectedResult,
CASE LAG([Event], 1, '') OVER (PARTITION BY CustomerID ORDER BY SequenceID)
WHEN [Event] THEN 1
ELSE 0
END AS Seq
FROM @Sample
)
SELECT CustomerID,
SequenceID,
[Event],
CurrentResult,
ExpectedResult,
DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY SequenceID - Seq) AS SwePeso
FROM cteSource
ORDER BY CustomerID,
SequenceID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Kman287
Starting Member

5 Posts

Posted - 2014-07-06 : 16:09:12

Hi SwePeso,

Thanks for your help! It seems to be partially working. It appears that logic doesn't work if the same Event is repeated multiple times in a sequence. See amended code below:

DECLARE @Sample TABLE
(
CustomerID TINYINT NOT NULL,
SequenceID TINYINT NOT NULL,
[Event] CHAR(1) NOT NULL,
CurrentResult TINYINT NOT NULL,
ExpectedResult TINYINT NOT NULL
);

INSERT @Sample
(
CustomerID,
SequenceID,
[Event],
CurrentResult,
ExpectedResult
)
VALUES (1, 1, 'A', 4, 1),
(1, 2, 'A', 4, 1),
(1, 3, 'B', 3, 2),
(1, 4, 'C', 2, 3),
(1, 5, 'A', 4, 4),
(1, 6, 'A', 4, 4),
(1, 7, 'E', 1, 5),
(1, 8, 'D', 5, 6),
(2, 1, 'B', 3, 1),
(2, 2, 'C', 2, 2),
(2, 3, 'C', 2, 2),
(2, 4, 'C', 2, 2),
(2, 5, 'C', 2, 2),
(2, 6, 'C', 2, 2),
(2, 7, 'C', 2, 2);

-- SwePeso
SELECT s.CustomerID,
s.SequenceID,
s.[Event],
s.CurrentResult,
s.ExpectedResult,
DENSE_RANK() OVER (PARTITION BY s.CustomerID ORDER BY s.SequenceID - CASE WHEN s.[Event] = y.[Event] THEN 0 ELSE 1 END) AS SwePeso
FROM @Sample AS s
LEFT JOIN @Sample AS y ON y.CustomerID = s.CustomerID
AND y.SequenceID = s.SequenceID + 1
ORDER BY s.CustomerID,
s.SequenceID;

Kman287
Starting Member

5 Posts

Posted - 2014-07-06 : 16:14:29

I meant to add that the issue still remains for the last for rows where value is still being incremented. From what I understand, this solution works if one event is duplicated within the same sequence but continue to increment when facing 3+ values in a sequence.

Thanks again!
Kman287

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 16:34:40

[code]DECLARE @Sample TABLE
(
CustomerID TINYINT NOT NULL,
SequenceID TINYINT NOT NULL,
[Event] CHAR(1) NOT NULL,
CurrentResult TINYINT NOT NULL,
ExpectedResult TINYINT NOT NULL
);

INSERT @Sample
(
CustomerID,
SequenceID,
[Event],
CurrentResult,
ExpectedResult
)
VALUES (1, 1, 'A', 4, 1),
(1, 2, 'A', 4, 1),
(1, 3, 'B', 3, 2),
(1, 4, 'C', 2, 3),
(1, 5, 'A', 4, 4),
(1, 6, 'A', 4, 4),
(1, 7, 'E', 1, 5),
(1, 8, 'D', 5, 6),
(2, 1, 'B', 3, 1),
(2, 2, 'C', 2, 2),
(2, 3, 'C', 2, 2),
(2, 4, 'C', 2, 2),
(2, 5, 'C', 2, 2),
(2, 6, 'C', 2, 2),
(2, 7, 'C', 2, 2),
(3, 1, 'A', 4, 1),
(3, 2, 'A', 4, 1),
(3, 3, 'B', 3, 2),
(3, 4, 'C', 2, 3),
(3, 5, 'A', 4, 4),
(3, 6, 'A', 4, 4),
(3, 7, 'E', 1, 5),
(3, 8, 'D', 5, 6),
(4, 1, 'B', 3, 1),
(4, 2, 'C', 2, 2),
(4, 3, 'C', 2, 2);

-- SwePeso
SELECT s.CustomerID,
s.SequenceID,
s.[Event],
s.CurrentResult,
s.ExpectedResult,
DENSE_RANK() OVER (PARTITION BY s.CustomerID ORDER BY ISNULL(f.SequenceID, 0)) AS SwePeso
FROM @Sample AS s
OUTER APPLY (
SELECT TOP(1) x.SequenceID
FROM @Sample AS x
WHERE x.CustomerID = s.CustomerID
AND x.SequenceID < s.SequenceID
AND x.[Event] <> s.[Event]
ORDER BY x.SequenceID DESC
) AS f;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Kman287
Starting Member

5 Posts

Posted - 2014-07-06 : 16:48:24

Hi SwePeso,

Thanks a lot! I think it is working I now have to understand how this actually works, I am definitely not familiar with OUTER APPLY but from what I read it seems to evaluate each from the second table expression!

I will read further to ensure I fully understand this and can hopefully start helping other members when I get more comfortable with it.

Thanks!
Kman287

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources