@dishdy

The problem is made slightly complicated by telling you that a number on a card can be repeated in a sequence of 1 or more occurences. And this can happen only once on a card! Hope this helps make things clearer.

Just to check I understand, does this mean that 10->11, 11->11, 11->11, 11->11, 11->13 is possible but 10->11, 11->11, 11->11, 11->11, 11->13, 13->13 is not possible? 

Not that it matters much right now as my brain has blown a fuse trying to figure out how to handle multiple "no change" rows... :o(

@dishdy

The problem is made slightly complicated by telling you that a number on a card can be repeated in a sequence of 1 or more occurences. And this can happen only once on a card! Hope this helps make things clearer.

@MisterMagoo

Just to check I understand, does this mean that 10->11, 11->11, 11->11, 11->11, 11->13 is possible but 10->11, 11->11, 11->11, 11->11, 11->13, 13->13 is not possible? Not that it matters much right now as my brain has blown a fuse trying to figure out how to handle multiple "no change" rows... :o(

Both are possible!
Maybe it would have been better to say: And this can happen only once on a card for a given number.

Am I correct in assuming that any given chain can be of completely arbtrary length?
That is, [Changes] can be 100,000.

@Rick Bielawski

Am I correct in assuming that any given chain can be of completely arbtrary length? That is, [Changes] can be 100,000.

That's correct.

Here is some more data for logic testing:

INSERT TC47(OldCardNo,NewCardNo)
SELECT 2,2 UNION ALL
SELECT 100,10 UNION ALL
SELECT 4,100 UNION ALL
SELECT 2,5 UNION ALL
SELECT 77,77 UNION ALL
SELECT 77,77 UNION ALL
SELECT 88,88 UNION ALL
SELECT 100,100 UNION ALL
SELECT 10,10 UNION ALL
SELECT 99,3 UNION ALL
SELECT 100,100 UNION ALL
SELECT 20,30 UNION ALL
SELECT 50,60 UNION ALL
SELECT 60,70 UNION ALL
SELECT 5,99

-- expected result:

FirstCardNo LastCardNo  Changes
----------- ----------- -----------
2           3           4
4           10          5
20          30          1
50          70          2
77          77          2
88          88          1

And here is a script for performance testing:

-- tc47_perftest

-- parameters
declare @m float=0.01 -- card change probability
declare @n int=10000  -- number of real changes
declare @s float=0.2  -- probability for duplicates

if object_id('tempdb..#c') is not null drop table #c
create table #c (card int, sort int, sort2 int, number int)

set nocount on
declare @i int, @j int, @k int
declare @card int, @sort int

set @i=rand(1) -- repeatable seed

begin tran
set @k=0
set @card=1
while @k<@n begin
    if rand()<@m set @card=@card+1
    set @sort = rand()*1000000000
    insert into #c (card, sort, sort2, number)	values (@card, @sort, rand()*1000000000, @k)
    -- add duplicates
    while rand()<@s 
    	insert into #c (card, sort, sort2, number)	values (@card, @sort, rand()*1000000000, @k)
    set @k=@k+1
end


commit

--select * from #c order by card, sort

IF OBJECT_ID('TC47','U') IS NOT NULL BEGIN
    DROP TABLE TC47
END
CREATE TABLE dbo.TC47 (
    SrNo INT IDENTITY,
    OldCardNo INT NOT NULL,
    NewCardNo INT NOT NULL
)

;with
cte1 as (
    select card, number, row_number() over(partition by card order by sort) as rn, sort, sort2
    from #c
)
, cte2 as (
    select a.card, a.number as oldcardno, b.number as newcardno, a.sort, a.sort2
    from cte1 a
    join cte1 b
    on a.rn=b.rn-1
    and a.card=b.card
)
insert into tc47 (oldcardno, newcardno)
select oldcardno,newcardno 
from cte2
order by sort2

drop table #c

select * from tc47

My stats:

(98 row(s) affected)
Table 'Worktable'. Scan count 9906, logical reads 139812, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC47'. Scan count 8, logical reads 264, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 365 ms.

Is there any posibility that any cardNo is Alloted to some another card.

Like 10>>20 and that previous 10 is assign to 25>>10. Is this possible?

@Alok Chandra Shahi

Is there any posibility that any cardNo is Alloted to some another card. Like 10>>20 and that previous 10 is assign to 25>>10. Is this possible?

So your question is: can the input be
25,10
10,20
The answer is obviously yes.

Secondly, look at rule 3 which says a number can be used only on one card.
But you need to keep separate the concept of a card and the concept of a number written on a card.
Here a card is just a piece of paper on which you can write a number followed by erasing that number and writing another number (or the same number) in its place.
The input table tells you the change from one number to another. But it doesn't tell you 'directly' on which card this happens. But because of the rules, the final result can be determined such that each output row corresponds to a card which had a certain number written on it in the beginning and a certain number written on it at the end (plus the number of links in the chain from the first number to the last number).

>> And here is a script for performance testing:

Stefan_G; Since your performance test code is essentially adding, I am right in thinking that you can summarise the expected results as follows...?

INSERT INTO tc47_expected (firstcardno, lastcardno, changes)
SELECT MIN(oldcardno), MAX(newcardno), COUNT(1) FROM #d
GROUP BY card
ORDER BY MIN(oldcardno)

where #d is:

WITH cte1 AS (...), cte2 AS (...)
SELECT card, oldcardno, newcardno
INTO #d
FROM cte2
ORDER BY sort2

and tc47 is:

INSERT INTO tc47 (oldcardno, newcardno)
SELECT oldcardno,newcardno FROM #d

I got

(98 row(s) affected) Table 'Worktable'. Scan count 12438, logical reads 214967, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC47'. Scan count 3, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 422 ms, elapsed time = 480 ms.

regards

David