Re: Random card insertion
I'm not sure about that range you described.
Your Range is something like
2100009199999 ,
2140000199999,
2103560199999 etc.
Meaning:
"21", followed by 5 digits, followed by "199999"
That's what i read from your description
If i look at your SQL, i get something different
Code:
DECLARE @startNumber bigint
DECLARE @endNumber bigint
set @startnumber = 2100000199990 //ENDS on 0 !!!
set @endnumber = 2100000199999 //ENDS on 9
That is something different.
What's correct?
In any case: The compare against existing cards should be doable with a recursive CTE, creating all card-numbers you can, then LEFT JOIN to the existing card-numbers and then eliminate the existing ones with an IS NULL in the WHERE-Clause
Something like Aircode
Code:
WITH
CTE(CTECardNo ) AS (SELECT CTECardNo FROM...... Here the CTE-Code to create all Card-Numbers in specified Range)
SELECT CTECardNo, CardNnumberIssued
FROM CTE LEFT JOIN SomeTable ON CTECardNo=CardNnumberIssued
WHERE CardNnumberIssued IS NULL
EDIT: Here's a fiddle
https://dbfiddle.uk/-EdyfzqT
Sample for recursive CTE building 10 CardNumbers acc. to your FIRST pattern
Code:
WITH
CTE(CN, CI) AS (
SELECT CAST('2100000199999' AS VARCHAR) AS CN, 0 AS CI
UNION ALL
SELECT CAST(CONCAT('21',Format(CI+1,'00000'),'199999') AS VARCHAR), CI+1 FROM CTE WHERE CI<10
)
SELECT CN, CI FROM CTE
Returns
| CN |
CI |
|---|
| 2100000199999 |
0 |
| 2100001199999 |
1 |
| 2100002199999 |
2 |
| 2100003199999 |
3 |
| 2100004199999 |
4 |
| 2100005199999 |
5 |
| 2100006199999 |
6 |
| 2100007199999 |
7 |
| 2100008199999 |
8 |
| 2100009199999 |
9 |
| 2100010199999 |
10 |
Re: Random card insertion
I‘m an idiot.
there is no need to cast back and forth to varchar.
Can be replaced with basevalue + (ci+1) * 1 million
tomorrow….
Re: Random card insertion
Yes.
Thank cuz I'm a little confused :P
Also the cards should start over 2100010199999 and end at 2199999999999
It might be possible to issue another code so that they will start at 22 and avoid all the trouble but I have to see if the system supports it and that could take a while as I have to get approval, go to the accounting go to the lawyers go to the management etc :P
Re: Random card insertion
Huh? MSSQL is weird.
Still have to cast the Anchor and the recursive part.
Ah well.... here not as Varchar, but Bigint
https://dbfiddle.uk/y6RJF4eC
Code:
WITH
CTE(CN, CI) AS (
SELECT CAST(2100000199999 AS BIGINT) AS CN, 0 AS CI
UNION ALL
SELECT CAST(2100000199999+(CI+1)*1000000 AS BIGINT),
CI+1 FROM CTE WHERE CI<10 /* Replace "10" with your Upper Bound */
)
SELECT CN, CI FROM CTE
Adjust the factor "1000000" if you want resulting values in a different "range"
like, removing the factor, the first number would be
2100000199999
the second
2100000200000
the third
2100000200001
and so on....
if factor is 100, the last 2 digits stay "99"
factor is 1000, the last 3 digits stay "999"
and so on
Re: Random card insertion
Thanks but I fail to see how this is random.
It's an addition of 10 over values.
Also since I want millions if I raise the CI to 1000 I get "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
Maybe I did not explain it well.
If I need 10, 4 random cards over 2100010199999 , I would be expecting p.e. 2152010199999, 2160318199999,2105510369279,2166418799991 .
Thanks.
Re: Random card insertion
Quote:
Originally Posted by
sapator
Thanks but I fail to see how this is random.
It's an addition of 10 over values.
Also since I want millions if I raise the CI to 1000 I get "The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
Ah Crap. Forgot that one (i rarely have to deal with MSSQL).
After the last "FROM CTE" add "option (maxrecursion 0)"
"... FROM CTE option (maxrecursion 0)"
maxrecursion 0 is infinite recursion until it hits the break out criterium
Quote:
Maybe I did not explain it well.
If I need 10, 4 random cards over 2100010199999 , I would be expecting p.e. 2152010199999, 2160318199999,2105510369279,2166418799991 .
Thanks.
Ah... OK. Got you. Can be done with SQL, but you still have the problem, that the "random" CardNo might exist already (as you wrote).
My approach was to generate a "Sequence" of possible CardNumbers, LEFT JOIN them to the existing CardNumbers, and then eliminate the existing ones with an IS NULL in the WHERE-Clause
(see snippet above).
This resulting set would contain all possible CardNumbers which definitely DON'T already exist
Just add a ROW_NUMBER to THAT resultset (ROW_NUMBER would return "1 To Count" of the "remaining" CardNumbers)
and then SELECT from that using RAND-Function in the Range 1 To Count, comparing the result of RAND to ROW_NUMBER to pick the CardNumber
As for RAND-Function: https://www.w3schools.com/sql/func_sqlserver_rand.asp
For performance, it might be an idea to dump the generated CardNumbers into a temp. Table instead of using a CTE
Re: Random card insertion
Hey.
Are we talking on actually adding a new column named ROW_Number or are we talking about the Row_Number over function?
On both cases I have a gap on what to do. If, I think, we are talking about adding another column then what exactly do I do with rand?
"in the Range 1 To Count, comparing the result of RAND to ROW_NUMBER" I don't get it sorry.
Re: Random card insertion
Quote:
Originally Posted by
sapator
Hey.
Are we talking on actually adding a new column named ROW_Number or are we talking about the Row_Number over function?
On both cases I have a gap on what to do. If, I think, we are talking about adding another column then what exactly do I do with rand?
"in the Range 1 To Count, comparing the result of RAND to ROW_NUMBER" I don't get it sorry.
ROW_NUMBER-Function
Imagine the CTE (or temp. table) contains the possible CardNumbers, say exactly 1M Numbers
2100010199999, 2100010200000,2100010200001.........2299999999999
Now you have a Table somewhere, which alread contains used CardNumbers (at least i understood you that way)
2100010200001, 2200011223344 and so....
You LEFT JOIN the temp. Table to that Table
Code:
SELECT TempTable.CardNumber, MyTable.CardNumberUsed
FROM TempTable
LEFT JOIN MyTable ON TempTable.CardNumber=MyTable.CardNumberUsed
WHERE MyTable.CardNumberUsed IS NULL
This returns all CardNumbers from TempTable, that DON'T already exist in MyTable
And on THAT Resultset you apply ROW_NUMBER
Code:
WITH CTE AS (SELECT TempTable.CardNumber, MyTable.CardNumberUsed
FROM TempTable
LEFT JOIN MyTable ON TempTable.CardNumber=MyTable.CardNumberUsed
WHERE MyTable.CardNumberUsed IS NULL)
SELECT CTE.CardNumber, ROW_NUMBER() OVER(ORDER BY CTE.CardNumber) AS RN FROM CTE
This returns all "available" (unused) cardnumbers, with RN being something like a "virtual" ID, running from 1 to COUNT(CardNumbers)
And on THAT result you apply RAND
Code:
WITH
CTE AS (SELECT TempTable.CardNumber, MyTable.CardNumberUsed
FROM TempTable
LEFT JOIN MyTable ON TempTable.CardNumber=MyTable.CardNumberUsed
WHERE MyTable.CardNumberUsed IS NULL),
C2 AS (SELECT CTE.CardNumber, ROW_NUMBER() OVER(ORDER BY CTE.CardNumber) AS RN FROM CTE),
MR AS (SELECT MAX(RN) AS MRN FROM C2)
SELECT C2.CardNumber FROM C2
INNER JOIN MR ON 1=1
WHERE C2.RN=FLOOR(RAND()*(MR.MRN-1+1)+1);
Re: Random card insertion
Hello.
This is very confusing for me especial the ROW_NUMBER function, I always get confused on what it does. So I will have to keep that as a future solution and check it.
Of course you explained it very well but even with that I get confused :P
One thing tho. I don't have a table with unused cards. I will have to manually created the cards. So I have a table than contains cards from 2100000000000 2100000199999 . These are in the DB created (some assigned some not but ready to be used) the card upper to 2100000199999 I have to do an insert.
Re: Random card insertion
Quote:
the card upper to 2100000199999 I have to do an insert.
Do you mean "above" (greater than) 2100000199999?
And those would definitly be unused/unassigned?
Re: Random card insertion
Yes.
The range would be something like 2100000200000 -- 2199999999999
Of course I can create a temp table put a gozillion cards in there do the left join and then delete the table.
Re: Random card insertion
Come to think of it, instead of left joining , doing row functions and stuff.
Is there a way to pick 1.000.000 randoms with a range 2100000200000 -- 2199999999999 and put it in a table?
That would be easier as I can insert from the temp table to the new table and be done with it.
Something like this with rand. (the limit still bugs out if I use 10000)
Code:
WITH
CTE(CN, CI) AS (
SELECT CAST(2100000199999 AS BIGINT) AS CN, 0 AS CI
UNION ALL
SELECT CAST(2100000199999+(CI+1)*1000000 AS BIGINT),
CI+1 FROM CTE WHERE CI<1000 /* Replace "10" with your Upper Bound */
)
SELECT CN, CI FROM CTE option (maxrecursion 0)
*1000000 AS BIGINT
*random AS BIGINT something like so
EDIT:
Go damn! Searching for an hour!
So this is a start.
select FLOOR(RAND() * (2199999999999- 2100000200000 + 1)) + 2100000200000;
That will generate the range.
Now with the initial query I can get a range, do a loop and put on a tobedeleted table. I can then insert to the live table.
Re: Random card insertion
Just came out of a hour long meeting, myself, and saw your answer.
Yes, it would have been exactly my approach.
Notabene: As unlikely it might be, you would still have to check if a newly drawn random Cardnumber has been already inserted (say 800,000 numbers ago)
Re: Random card insertion
Ye I think the first query covers that
Code:
IF NOT EXISTS (SELECT * FROM cognetic_members_card WHERE card_cardNumber = CONVERT(nvarchar(50), @i))
INSERT into cognetic_members_card (card_cardNumber,card_status)
values (CONVERT(nvarchar(50), @i),5)
... blah
I can understand the long hours meetings. We usually do teams meetings as most of our partners are scattered around and it's frustrating if you have work at hand. In location meetings are more joyful , we get a snack, full around, eat, do the 1 hour meeting, full around again... And this is coming from meetings of a big Greece company so they are "serious" :P
