Rauf Miah (6/24/2013)THANK YOU FOR NICE RESPONSE. THE CODE DOES THE TRICK. BUT SOON I LIKE TO LEARN THE TALLY.

Especially if one spent most of their formative years programming with linear-based languages like php or classic ASP as I did, getting rid of a looping mindset is difficult. For a long time I wrote SQL code as if it was just another version of ASP.

It took a lot of effort and re-writes and PRACTICE to learn that good SQL coding is SET based. That means every query retrieves the data as a SET (i.e., table) and not just a collection of rows that must be traversed "row-by-agonizing-row" (RBAR as Jeff Moden calls it).

The best way to turn your mindset around on this will be to take some of your simpler code that contains a WHILE loop or CURSOR loop and using examples from the dozens of posts and articles here on SQLServerCentral, work on making use of a tally table to eliminate the traditional loop. It's a hard concept to visualize at first, but keep practicing. And especially on any new work, when it looks like you need to write a loop, just say "No!" and work on a set based method. This may slow down your development time at first, but eventually it will come together in your brain and you will find it hard to think any other way.