You might have a table with different PEOs for each Company ID. For example:
Table name : tblRankingCid peo----------- -----------1 200003311 200006301 200009301 200012312 200003312 200006302 200009302 20001231
To give PEO ranking for each CompanyId, add one column, say “Rank” to the present table and use the following script:
--///////////////////////////////////////ALTER TABLE tblRanking ADD Rank int NULLdrop table #fiddeclare @Start intdeclare @FID intselect @Start = 0select distinct fid into #fid from tblRankingupdate t1 set @Start = Rank = case when t1.fid = @FID then @Start + 1 else1 end,@fid = t2.FID from tblRanking t1 ,#fid t2 where t1.fid = t2.fidselect * from tblRanking--///////////////////////////////////////fid peo rank----------- ----------- -----------1 20000331 11 20000630 21 20000930 31 20001231 42 20000331 12 20000630 22 20000930 32 20001231 4