• insert into #try values(2, 200,null,10)

    insert into #try values(3, 300,200,20)

    insert into #try values(4, 400,300,15)

    insert into #try values(5, 500,400,40)

    we need to get prior max based on source and destno. for each sourceno we need to get the destno count and out of that we need to get the prior max of those.

    in the above example.

    destno for 500 is 400 so we need to get count of that row.but 400 also has destno as 300 so get the count of 300 source and 300 sourceno has 200 destno so get the count of 200 but 200 sourceno doesn't have destno

    so we need to get the count and stop iterating.

    the prior max count of 500 sourceno will be max of ( 15,20,10) count . We should not include sourceno 500 count in the calculation.