T-SQL Count Multi-Column Changes

Question

I have a data like this:

a,x,1
a,x,2
a,y,5
a,z,5
a,t,5
a,s,6
b,x1,11
b,x1,21
b,y1,51
b,z1,51
b,t1,51

I want to count value changes but if 2nd field and 3rd field values isn't changed; this isn't a changing. Both 2nd and 3st field values must change.

In my example above; 1st row to 2nd row isn't a changing but 2nd row to 3rd row is a changing because both x and 2 values are changed. Again, 3rd row to 4th row is a changing.

I want to have result with query as

a,3
b,2

Thank you.

can you just post the required output..question is not clear — mohan111, Oct 27 '14 at 10:09
You say "Both 2nd and 3st field values must change."? How come "3rd row to 4th row is a changing"? 3rd field value remains the same(5). — kelsier, Oct 27 '14 at 10:36
According to your question there is only one change in both a and b that is from "2nd row to 3rd row". Dono in what basis you need that result. — Pரதீப், Oct 27 '14 at 11:00
changing means; both col2 and col3 must change. i want to count different changed rows. — MERT DOĞAN, Oct 27 '14 at 11:53
How do I know the change order? Is there an ID or date field that is used for sorting? Also, is the first value shown for Col1 considered a 'change'? — AHiggins, Oct 27 '14 at 14:19
AHiggins; there isn't any change order. Same rows can be in any order. — MERT DOĞAN, Oct 27 '14 at 16:25
What do you mean by "Same rows can be in any order"? If `a,x,1` is followed by `a,x,2` then according to your description this should not be considered a change, but if it was followed by `a,y,5` then it would be a change. Therefore, the order of rows matters and it is not defined in your case. Also, you haven't answered the question about whether the first row per column 1 is considered a change or not. — Andriy M, Nov 05 '14 at 10:31
@AndriyM this means that; a,x,2 isn't a different value because of a,x,1 has x value before. And then; it can place any row but it isn't a new change. In my example it is in 2nd row but it can place at 6st row instead. — MERT DOĞAN, Nov 05 '14 at 11:15
It looks like you want to [group all related rows](http://stackoverflow.com/questions/18618999/group-all-related-records-in-many-to-many-relationship-sql-graph-connected-comp) and count the groups. — Andriy M, Nov 05 '14 at 12:17

score 0 · Answer 1 · answered Oct 27 '14 at 11:29

0

From your question actually the count for both a and b is 1. Becoz only one time there is a change in both the rows.

CREATE TABLE #t
  (
     col1 VARCHAR(10),
     col2 VARCHAR(10),
     col3 INT
  )

INSERT INTO #t
VALUES      ('a','x',1),
            ('a','x',2),
            ('a','y',5),
            ('a','z',5),
            ('b','x1',11),
            ('b','x1',21),
            ('b','y1',51),
            ('b','z1',51),
            ('b','t1',51);

WITH cte
     AS (SELECT Dense_rank()
                  OVER(
                    partition BY col1
                    ORDER BY col2) col1_rn,
                Dense_rank()
                  OVER(
                    partition BY col1
                    ORDER BY col3) col2_rn,
                *
         FROM   #t)
SELECT a.col1,
       Count(1) AS [count]
FROM   cte a
       LEFT JOIN cte b
              ON a.col1 = b.col1
                 AND a.col1_rn = b.col1_rn + 1
                 AND a.col2_rn = b.col2_rn + 1
WHERE  b.col1_rn IS NOT NULL
GROUP  BY a.col1

answered Oct 27 '14 at 11:29

Pரதீப்

91,748
19
131
172

Thank you so much Pradeep. You are a professional i can see from your codes. But in my example, both a and b has change 2 times. in row1 and row2 equal based on col2 (because x values are same), in row3 and row4 equal based on col 3 (because 5 values are same). Thanks. – MERT DOĞAN Oct 27 '14 at 11:51
@MERTDOĞAN Seriously i am not getting your point. you are clearly mentioning "both col2 and col3 must change" then only row2('a','x',2) changed to row3('a','y',5). and row2('b','x1',21) changed to row3('b','y1',51) other than this i dont see both col2 and col3 changing. – Pரதீப் Oct 27 '14 at 12:38
of course you allright but my values data and yours isn't equal. If you try to use your code with my values data; it is still generating same values a,1 b,1. refresh this page and if you try your code with new data; you will see it is still generating same result. – MERT DOĞAN Oct 27 '14 at 13:19
@MERTDOĞAN yes obviously u will get that result becoz in 1st and 2nd row 'x' is same. next 3 rows '5' is same. only from 5 to 6 there is a change in both the columns so a =1 – Pரதீப் Oct 27 '14 at 13:31
Thanks for your replies Pradeep. 2 to 3 everything is changed and this is change number 1. 5 to 6 everything is changed and this is change number 2. I want to see this. – MERT DOĞAN Oct 27 '14 at 16:24

T-SQL Count Multi-Column Changes

1 Answers1