on SQL How to call values by date

Question

I have this SQL Query.

In the query below,

a random one of the data in the insert into the first part will be set today,

and a random one from the bottom insert into the data tomorrow.

And this will continue like this. How can I write this query?

select * from #tempAccessCode
declare @accessCode int=  ( select accessCode from #tempAccessCode)
declare @prefix int=  ( select prefix from #tempAccessCode)

------------------------------------------------
create table #TempPrefix(AccessCode nvarchar(max),Prefix nvarchar(max))


insert into #TempPrefix values ('1671','6710')
insert into #TempPrefix values ('1670','6700')                                   
insert into #TempPrefix values ('1669','6690')
insert into #TempPrefix values ('1668','6680')
insert into #TempPrefix values ('1667','6670')
insert into #TempPrefix values ('1666','6660')
insert into #TempPrefix values ('1665','6650')
insert into #TempPrefix values ('1664','6640')
insert into #TempPrefix values ('1663','6630')
insert into #TempPrefix values ('1662','6620')
----------------------------------------------
insert into #TempPrefix values ('1661','6610')
insert into #TempPrefix values ('1660','6600') 
insert into #TempPrefix values ('1659','6590')
insert into #TempPrefix values ('1658','6580')
insert into #TempPrefix values ('1657','6570')
insert into #TempPrefix values ('1656','6560')
insert into #TempPrefix values ('1655','6550')
insert into #TempPrefix values ('1654','6540')
insert into #TempPrefix values ('1653','6530')
insert into #TempPrefix values ('1652','6520')

declare @AccessCodee nvarchar(max)
declare @prefixx nvarchar(max)

Select top 1 @prefixx = Prefix,@AccessCodee=AccessCode  from #TempPrefix order by  newid()

Select @prefixx,@AccessCodee

drop table #TempPrefix
-------------------------------------------------

update  [CallList]  set AccessCode = @AccessCodee , Prefix = @prefixx 
where  CampaignId  =5409 and ListId=@listId

Answer 1

If I understand this correctly, the daily task is: Insert a random row from a set of rows:

• 1st day set = 6620 to 6710 (set #1)
• 2nd day set = 6520 to 6610 (set #2)
• 3rd day set = 6620 to 6710 (set #1 again)
• 4th day set = 6520 to 6610 (set #2 again)
• etc.

So it is two sets of which you alternately pick a row.

Once

Create a table containing the rows for each set.

create table datasets (setno int, accesscode int, prefix int);

insert into datasets (setno, accesscode, prefix)
values
  (1, 1662, 6620),
  (1, 1663, 6630),
  ...
  (1, 1671, 6710),

  (2, 1652, 6520),
  (2, 1653, 6530),
  ...
  (2, 1661, 6610),
  ;

Daily

Use modulo 2 on the current day to get the set number.

insert into CallList (date_created, accesscode, prefix)
select top(1) getdate(), accesscode, prefix
from datasets
where setno = datediff(day, '1900-01-01', getdate()) % 2 + 1
order by newid();

Answer 2

You can go for conditional UPDATE, whether there exists prefix already. If prefix (6620 to 6710) exists, go for different prefix(6520 to 6610).If prefix (6620 to 6710) does not exist, go for same prefix(6620 to 6710).

If you run update statement daily, this way, you will get the data randomly from one list(top list) one day and from second list(bottom list) another day.

DECLARE @AccessCodee NVARCHAR(max)
DECLARE @prefixx NVARCHAR(max)

IF NOT EXISTS (
        SELECT Prefix
        FROM [CallList]
        WHERE Prefix BETWEEN 6620
                AND 6710
        )
BEGIN
    SELECT TOP 1 @prefixx = Prefix
        ,@AccessCodee = AccessCode
    FROM #TempPrefix
    WHERE Prefix BETWEEN 6620
            AND 6710
    ORDER BY newid()

    UPDATE [CallList]
    SET AccessCode = @AccessCodee
        ,Prefix = @prefixx
    WHERE CampaignId = 5409
        AND ListId = @listId
END
ELSE
BEGIN
    SELECT TOP 1 @prefixx = Prefix
        ,@AccessCodee = AccessCode
    FROM #TempPrefix
    WHERE Prefix BETWEEN 6520
            AND 6610
    ORDER BY newid()

    UPDATE [CallList]
    SET AccessCode = @AccessCodee
        ,Prefix = @prefixx
    WHERE CampaignId = 5409
        AND ListId = @listId
END