Gary Johnson (10/16/2008)
Thanks for posting the sample data and DML.I'm not clear on what you want the output to be. If you can post that maybe we can come up with a way to do this without having to do this in a loop and instead do it set based.
Thank you for your prompt response Gary. Sorry I forgot to show you the output for the following data sample.
For this data sample, Item #1:
INSERT #TempTable
SELECT 1,'1','en','C' UNION ALL
SELECT 2,'1','de','C' UNION ALL
SELECT 3,'5','zh','A' UNION ALL --/first(1) batch of A
SELECT 4,'4','en','A' UNION ALL --/first(1) batch of A
SELECT 5,'3','en','A' UNION ALL --/first(1) batch of A
SELECT 10,'2','zh','A' UNION ALL --/first(1) batch of A
SELECT 11,'2','zh','A' UNION ALL --/first(1) batch of A <- dup on RowID 10 (checked)
SELECT 12,'1','zh','C' UNION ALL
SELECT 13,'1','de','C' UNION ALL
SELECT 14,'1','en','A' UNION ALL --/second(2) batch of A
SELECT 15,'3','en','A' UNION ALL --/second(2) batch of A
SELECT 16,'3','de','A' UNION ALL --/second(2) batch of A
SELECT 18,'1','en','A' UNION ALL --/second(2) batch of A <- dup on RowID 14 (checked)
SELECT 19,'3','de','A' UNION ALL --/second(2) batch of A <- I don't need to include this even if there is dup on RowID 16 since it is already covered in the range from RowID 14-18
SELECT 20,'1','en','C' UNION ALL
SELECT 21,'1','en','C'
Final Output on Item #1 should be:
SELECT 3 as RowID, 'A' as Code UNION ALL
SELECT 11 as RowID, 'A' as Code UNION ALL
SELECT 12 as RowID, 'C' as Code UNION ALL
SELECT 14 as RowID, 'A' as Code UNION ALL
SELECT 18 as RowID, 'A' as Code UNION ALL
SELECT 20 as RowID, 'C'
To explain on how did I come up with the final output on Item #1.
First, I need to get the interleaving rows per Code so it should give me the output of:
SELECT 3 as RowID, 'A' as Code UNION ALL
SELECT 12 as RowID, 'C' as Code UNION ALL
SELECT 14 as RowID, 'A' as Code UNION ALL
SELECT 20 as RowID, 'C'
Next is I need to process the interleaving rows of duplicates per batch of A's that would result on a unique range result. So for the first batch of A, it should give me the output.
SELECT 11 as RowID, 'A'
and the second batch of A will have an output:
SELECT 18 as RowID, 'A'
So that is how I came up with the final output. I put some comments on Item #1 hoping it would help understand the process.
Thank you for your time and help.