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.

key1dateproduct_idrank_within_key_and_productrank_within_keycounting

00120100403004111

00120100505003122

00120100615002133

00120100716001144

00120110813001254

00120110923001364

00220100403002111

00220100612002221

00220110503004133 (should be 2)

00220120103005144 (should be 3)

00320100612002111

00320100831005122

00320110214005232

00320110603005342

00320120803001155 (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!