kapil_kk (6/30/2013)
thnks its working.. but can you plz explain me this part of query:SUBSTRING(VoucherNo,2,LEN(VoucherNo)) - ROW_NUMBER() OVER(ORDER BY VoucherNo, TransNo)
Sure. I'm removing the character from the start of the voucherno as I needed a numerical value. I then added a row_number that was partitioned by the voucherno and the transno, if you minus the row_number from the numerical voucherno, it shows a grouping that I can see for the islands and gaps solution. You should check out Itzik Ben-Gan's solutions for this sort of problem. I think there's a solution in one of the SQL Server Deep Dives books, can't remember which volume.