All Forums Database
KVB 124 posts Joined 09/12
01 Aug 2014
Get the minimum date if the consecutive code is same

CT KBB(DT DATE,COD VARCHAR(1))
INS INTO KBB VALUES('2014-01-01','A');
INS INTO KBB VALUES('2014-01-02','A');
INS INTO KBB VALUES('2014-01-03','D');
INS INTO KBB VALUES('2014-01-04','A');
INS INTO KBB VALUES('2014-01-05','D');
INS INTO KBB VALUES('2014-01-06','A');
 
Here I have to get the 1,2,3,4,5,6 rows..
Thanks
KVB

KVB 124 posts Joined 09/12
01 Aug 2014

And I have used the below one and got it.
 

SEL EFF_DT,COD,RANK() OVER(ORDER BY EFF_DT RESET WHEN COD<>MAX(COD) OVER(ORDER BY EFF_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING))
FROM KBB
 
Correct me ,if I am wrong
 
 

 

You must sign in to leave a comment.