Sequential Token Management in sql server

Question

Post reply

Sequential Token Management in sql server

sivaay1223

Old Hand

Points: 334

September 28, 2019 at 9:48 am

#3684272

I am looking for a better solution to achieve following task

I have a table as shown below

CouponNo   Status      Sequence
A101      Completed       1
A102      Completed       2
A103      Completed       3
A104      Serving         4
A105      Pending         5
A106      Pending         6
A107      Pending         7
A108      Pending         8
A109      Pending         9
A110      Pending         10
A111      Pending         11
A112      Pending         12
A113      Pending         13
A114      Pending         14
A115      Pending         15

I have a case where tokens can be put on hold. Policy says when we resume hold tokens it should be placed after 3 tokens from the currently serving token In our example lets say A105 and A106 are put on hold. Then the table becomes

 CouponNo   Status      Sequence
    A101      Completed       1
    A102      Completed       2
    A103      Completed       3
    A104      Serving         4
    A105      Hold           -99
    A106      Hold           -99
    A107      Pending         5
    A108      Pending         6
    A109      Pending         7
    A110      Pending         8
    A111      Pending         9
    A112      Pending         10
    A113      Pending         11
    A114      Pending         12
    A115      Pending         13

Either I put sequence as -99 for all holded tokens or is it better to move them to HOLD table?

secondly when we resume both tokens, i want the table like below

CouponNo   Status      Sequence
        A101      Completed       1
        A102      Completed       2
        A103      Completed       3
        A104      Serving         4
      **A105      Pending         8**
      **A106      Pending         9**            
        A107      Pending         5
        A108      Pending         6
        A109      Pending         7
        A110      Pending         10
        A111      Pending         11
        A112      Pending         12
        A113      Pending         13
        A114      Pending         14
        A115      Pending         15

How we can achieve this by creating a common procedure.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Collins SSCrazy Points: 2159 More actions · Answer 2

You say you're looking for a "better solution" so could you please provide the current solution? Can't say if something is better if we can't see what it's being compared to. Is CouponNo a unique key of this table?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ben.brugman SSChampion Points: 13369 More actions · Answer 3

In General:

Keep the original data and add the modifications in another column. (Sequence hold the sequence number, status on Hold means that the sequence should only processed after sequence numbers up to two (or three) higher).

Because there is not enough description, some assumptions are made here.

The CouponNo does not have any order information and could/can be random. **)
There is no control over the number of hold statussen. ***)
When a sequence is altered into -99, information about the original sequence get lost.

So use the status column to determine where the 'actual' sequence should occure. So calculate 'dynamically' where the sequence fits into the current sequence. So keeping the original sequence number but when making decisions adding 3 or 2 to the sequence number.

Ben

**) If the CouponNo does have order information, there should not be a distinct Sequence column. (A column should not be derived from another column, because then it is depended on another column).

***) There is no description of the number of holds that can occur at one time or in total.