Cadavre (6/30/2013)
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.
thanks for your explanation.. 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/