0

I have the following dataset in SQL Server:

Name     Pattern
-------  -------
Peter    DRF  
Peter    DRF
Peter    BBB
Peter    DRF
Peter    CCC
Peter    CCC
Sue      DRF
Sue      CCC
Sue      DRF
Sue      VVV
Sue      SSS

I need to count the number of times a pattern changes per Name (not only distinct changes). So my end result will be:

Peter 3    
Sue 4

In other words, only counting the distinct changes. Peter changed from DRF to BBB to CCC and Sue changed from DRF to CCC to DRF to VVV to SSS.

Is this possible without using a cursor?

I have a start date and end date per row, but no unique id. Patterns gets logged by the system per person per day and sometimes multiple times per day per person.

  • What should the result be if we have the tuples: (1,1),(1,2),(1,1),(1,2)? – Lennart Apr 10 '18 at 11:48
  • 5
    How does SQL Server know that the two Peter/DRF rows are followed by Peter/BBB and then Peter/DRF again? Do you have another column that indicates order? It's important to remember that a table is just a bag of rows, and without a column to indicate order, there's no way to query in such a way that reliably indicates original insert order. – Aaron Bertrand Apr 10 '18 at 12:55
  • Please consider adding DDL and DML for your example to make it easier for people to work on your problem, thank you. – Andriy M Apr 11 '18 at 7:20
-1

First you need row_number/serial number.

;With CTE as
(
select *
,row_number()over(order by name)rn
 from @t
)

select t.name
,sum(case when t1.rn=t.rn +1 and t.pattern<>t1.pattern then 1 else 0 end)Result
from cte t
cross apply(select t1.rn,t1.pattern from cte t1
where t.name=t1.name
)t1
group by t.name

if output is ok across all data then it will perform ok.

| improve this answer | |
  • @PierreBarnard I'm sorry, but there is no way this query solves the problem you asked about. It depends on the order in which rows are returned, which is completely arbitrary and not guaranteed, and will almost certainly change over time as your table grows and the pages split (especially since it sounds like you don't have a primary key, so your table is probably stored as a heap). I think it will work if you specify the correct order in the CTE, but then it is basically a more complicated version of several other answers here, including my own. – Scott M Apr 11 '18 at 13:11
  • @PierreBarnard , if output is not correct then let me know.I wrote query As per you sample,we are not discussing whole database design and query plan.you can always discuss performance issue showing correct table structure etc. – KumarHarsh Apr 12 '18 at 3:19
2

Try this:

Declare @T table (name varchar(10), Pattern varchar(10))
insert into @t (name, Pattern) values
('Peter', 'DRF'),
('Peter', 'DRF'),
('Peter', 'BBB'),
('Peter', 'DRF'),
('Peter', 'CCC'),
('Peter', 'CCC'),
('Sue', 'DRF'),
('Sue', 'CCC'),
('Sue', 'DRF'),
('Sue', 'VVV'),
('Sue', 'SSS')

SELECT NAME
    ,count(DISTINCT pattern) AS DistinctChanges
FROM @t
GROUP BY NAME

| name  | DistinctChanges |
|-------|----------------|
| Peter | 3              |
| Sue   | 4              |

Note: There is a difference between distinct values and the number of times a value changed assuming some kind of timeline.

For example: AAA, BBB, AAA, BBB, AAA, BBB, AAA would be 2 distinct changes, but 5 total times the value changed assuming some sort of order.

Which variation are you looking for?

| improve this answer | |
  • Thanks Scott, you are 100% correct, that is a crucial question and the answer I need is "5 total times" not 2 distinct changes. Aspologies for not correctly clarifying. – Pierre Barnard Apr 11 '18 at 7:08
0

Yes, this is possible without a cursor. By the way you worded your question, I'm assuming you have another column (or columns) you haven't told us about that defines how the rows should be ordered. In my example below I've used an identity column, but you could replace that with whatever column(s) give the proper order of your rows. I'm also assuming you are on at least SQL Server 2012.

create table #T (Id int identity, Name varchar(10), Pattern varchar(10))
insert into #T (Name, Pattern) values
('Peter', 'DRF'),
('Peter', 'DRF'),
('Peter', 'BBB'),
('Peter', 'DRF'),
('Peter', 'CCC'),
('Peter', 'CCC'),
('Sue', 'DRF'),
('Sue', 'CCC'),
('Sue', 'DRF'),
('Sue', 'VVV'),
('Sue', 'SSS')
;

WITH ChangeCounter AS
(
  SELECT *,
    LAG(Pattern) OVER (PARTITION BY Name ORDER BY Id) AS PrevPattern,
    CASE WHEN LAG(Pattern) OVER (PARTITION BY Name ORDER BY Id) <> Pattern THEN 1 ELSE 0 END AS PatternChanged
  FROM #T
)
SELECT Name, SUM(PatternChanged) AS NumChanges
FROM ChangeCounter
GROUP BY Name

This solves the problem in two parts. In the CTE, it uses the LAG function to check each row and mark whether it is different from the previous row (1) or not (0). Then in the main query it adds up the number of rows that were different from the previous row, which is by definition the number of times the Pattern value changed.

| improve this answer | |
  • Thanks Scott, no, I do not have a unique identifier ID, but I do have a start date and end date per row. – Pierre Barnard Apr 11 '18 at 7:09
  • @PierreBarnard I never said anything about a unique identifier ID, only that you need a column you can order by. As your response indicates, you have a start date and end date, one of which (you haven't told us which) should be used for ordering the rows. So just replace ORDER BY Id with ORDER BY [Start Date] or ORDER BY [End Date] in this query. – Scott M Apr 11 '18 at 13:07
0

First thanks to Scott Hodgin to build and post an minimal example.

You can flag every change in this way:

coalesce(lag(pattern) over (partition by name order by id), '')

Then simply SUM grouping by name to get distinct changes:

declare @t table (id int, name varchar(10), Pattern varchar(10));

insert into @t values
(1, 'Peter', 'DRF'),
(2, 'Peter', 'DRF'),
(3, 'Peter', 'BBB'),
(4, 'Peter', 'DRF'),
(5, 'Peter', 'CCC'),
(6, 'Peter', 'CCC'),
(7, 'Sue', 'DRF'),
(8, 'Sue', 'CCC'),
(9, 'Sue', 'DRF'),
(10, 'Sue', 'VVV'),
(11, 'Sue', 'SSS');

select name,
       sum(rst) as distinct_changes
from  (select id, name,
       iif(coalesce(lag(pattern) over (partition by name order by id), '') <> pattern, 1, 0) rst
       from   @t) g
group by name;
name  | distinct_changes
:---- | ---------------:
Peter |                4
Sue   |                5

dbfiddle here

| improve this answer | |
0

Just adding another solution to your problem that is using a sub query:

select a.Name,count(a.Name) 
from
    (select [Name],count(Pattern) as b from Table_1
        group by [Name],pattern) as a
group by a.Name

The first query part was to get count of each Pattern per Name that was achieved by:

select [Name],count(Pattern) as b from Table_1
    group by [Name],pattern

And the next part was to get the count of Patterns associated with each Name as:

select a.Name,count(a.Name) 
from
    (select [Name],count(Pattern) as b from Table_1
        group by [Name],pattern) as a
group by a.Name
| improve this answer | |
  • Thanks, the code works, but as @ScottHodgin explained above, I need EVERY instance of the pattern change – Pierre Barnard Apr 11 '18 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.