TSQL Challenge 47

Use this thread to discuss TSQL Challenge 47

Hmm, how did I get here? Is there a time machine or something? ;)

Yes, you got into a time machine :-)

How does

Rule 1 . The order of the events registered in the table is arbitrary.

Sit with the Overall Job Requirement? "Your job is to write a query that shows the first number, current number (most recent) and the number of changes made."

What is the first number and the current number for an arbitrary set of numbers?

MisterMagoo:

How does

Rule 1 . The order of the events registered in the table is arbitrary.

Sit with the Overall Job Requirement? "Your job is to write a query that shows the first number, current number (most recent) and the number of changes made."

What is the first number and the current number for an arbitrary set of numbers?

The events can be chained together in only one way. The beginning of the chain is the first card number. The end of the chain is the last card number.

Funnily enough, I was just coming back to edit my post because I had that "Doh!" moment myself!

 

Thanks.

Is it possible for two chains to have same starting card number like below? In that case, the o/p will have two records for 10 => 10 - 18 and 10 - 43. Please confirm.

10 - 12
12 - 18
--
--
10 - 41
41 - 43

Rule 3 does not allow this.

A script to create some simple test data is below

 

DECLARE @numberOfRecords AS INTEGER

DECLARE @lastCard AS INTEGER

DECLARE @chainNumber AS INTEGER

SET @chainNumber = 1

SET @lastCard = 1

WHILE @chainNumber < 10       -- <== Number of card chains

BEGIN

      SET @numberOfRecords = 0

      --SET @lastCard = @lastCard+1

      SET @lastCard = @chainNumber * 1000

      WHILE @numberOfRecords < 10         -- <= Number of changes in each chain

      BEGIN

            IF @numberOfRecords % (@chainNumber+1) = 0

                  INSERT INTO TC47 (OldCardNo, NewCardNo)

                  SELECT @lastCard,@lastCard

            ELSE

            BEGIN

                  INSERT INTO TC47 (OldCardNo, NewCardNo)

                  SELECT @lastCard,@lastCard+1

                 

                  SET @lastCard = @lastCard  + 1

            END

           

            SET @numberOfRecords = @numberOfRecords + 1

      END

     

      SET @chainNumber = @chainNumber + 1

END

The results I get are

FirstCardNo LastCardNo Changes
1000 1005 10
2000 2006 10
3000 3007 10
4000 4008 10
5000 5008 10
6000 6008 10
7000 7008 10
8000 8008 10
9000 9009 10

 

 

 

But remember:
1. they can be in the table in any order
2. the chain of values can increase and decrease

Thus shuffle things a bit to make it interesting. Adding the foillowing to the end of your script should produce the same result.

select NEWID() s,OldCardNo,NewCardNo
into #tc47
from tc47

truncate table tc47

insert into tc47
select OldCardNo,NewCardNo
from #tc47
order by s

drop table #tc47
update tc47 set OldCardNo=OldCardNo-50 where right(CONVERT(varchar,OldCardNo),1)='2'
update tc47 set NewCardNo=NewCardNo-50 where right(CONVERT(varchar,NewCardNo),1)='2'
update tc47 set OldCardNo=OldCardNo-50 where right(CONVERT(varchar,OldCardNo),1)='4'
update tc47 set NewCardNo=NewCardNo-50 where right(CONVERT(varchar,NewCardNo),1)='4'

select * from tc47

OK, fixed that script.

for source :

 

SrNo        OldCardNo   NewCardNo
----------- ----------- -----------
1           2           2
2           2           1
3           10          11
4           11          11
5           11          12
6           101         101
7           100         101
8           1000        1000
9           5               6


the output:

FirstCardNo LastCardNo Changes
----------- ---------- -------
2           1                     2
5           6                    1
10          12                3
100         101             2
1000        1000          1

Here, I don't get why first card number is 100 where srno 6 in source there is 101 as first card number , why there is 100 as first card no. in expected result ? 

and what does this example means?
For example: 10=>20, 20=>20, 20=>20, 20=>30 is OK. But 
10=>20, 20=>20, 20=>30, 30=>20 is not allowed in the data. 

The chain is 100 => 101 => 101    (so row 7 then row 6)

The first card in the chain is 100,

The last card is 101

The number of rows processed is 2

 

Regards

David

 

bharat:

Here, I don't get why first card number is 100 where srno 6 in source there is 101 as first card number , why there is 100 as first card no. in expected result ?
and what does this example means?
For example: 10=>20, 20=>20, 20=>20, 20=>30 is OK. But
10=>20, 20=>20, 20=>30, 30=>20 is not allowed in the data.

bharat,
I too did not understand the problem immediately.
It helps to think of the cards as objects.
A single row in the input table indicates how the number on a card 'changes'.
The 'change' could actually be such that the number on the card remains the same.
For a single row in the input table you actually do not know which card it refers to!
Furthermore, the table contains these changes in an arbitrary sequence, i.e. not necessarily in the order in which they occured in time.
But by telling you that a number can appear on only one card, it becomes possible to construct a sequence of number changes for a given card.
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.

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(

MisterMagoo:

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(

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