Hi, Gullimeel and SQL Kiwi,

Thank you very much! The SQLs posted work perfect.

There are 20TB DBs in total to make switching matrix with SQL2008R2 for Markov chain analysis with OR...

Yes, I did.

Plz see key1=002 and 003 with rank_within_key_product.

key1 date product_id rank_within_key_and_product rank_within_key counting

001 20100403 004 1 1 1

001 20100505 003 1 2 2

001 20100615 002 1 3 3

001 20100716 001 1 4 4

001 20110813 001 2 5 4

001 20110923 001 3 6 4

002 20100403 002 1 1 1

002 20100612 002 2 2 1

002 20110503 004 1 3 3 (should be 2)

002 20120103 005 1 4 4 (should be 3)

003 20100612 002 1 1 1

003 20100831 005 1 2 2

003 20110214 005 2 3 2

003 20110603 005 3 4 2

003 20120803 001 1 5 5 (should be 3)

Hi, Gullimeel.

I did run your

select key1, date, product_id, dense_rank() over (partition by key1 order by product_id) as product_id_sw

from table2 order by key1, date;

Using 'dense_rank'does not count 'switching' product_id.

Here is...

Hello Eugene,

I tried some scripts from the article you listed.

But, it did not work and is very tough to understand Quirky Update.

Please help me to write correct scripts.

Sample Data:

create...

I checked the dense_tank sql with other sample. I am wrong and stacked

Sample data:

create table table2

(

key1 varchar(16),

date varchar(16),

product_id varchar(16),

value1 integer

);

insert into table2 values ('001','20100505','003',300);

insert into table2 values ('001','20100615','002',200);

insert into table2...

Thanks, Eugene Elutin.

I just did run your sql in my original pos data. It has same result of SAS!

It works fine!!

Thanks Anthony!

I have read the article, Forum Etiquette. I will be careful next time.

Thanks!