Get result based on competition like FIFO

Question

Post reply

Get result based on competition like FIFO

Qira

SSCommitted

Points: 1930
More actions
October 29, 2011 at 9:23 pm

#390719

I've tables and data as following,
declare @t1 table
(idx int identity, programID int, quota int, filled int, dtVersion timestamp);
/*programID is unique*/
insert into @t1(programID, quota, filled) values(1, 100, 23);
insert into @t1(programID, quota, filled) values(2, 80, 9);
insert into @t1(programID, quota, filled) values(3, 70, 11);
insert into @t1(programID, quota, filled) values(4, 100, 39);
insert into @t1(programID, quota, filled) values(5, 50, 47);
declare @tResult table
(idx int identity, matrixNo int, programID int);
/*
Combination of matrixNo and programID will generate a unique row
programID is foreign key to @t1(programID)
*/
The scenario as following,
1. Each student (based on matrixNo in @tResult) will apply any Program (based on ProgramID in @t1)
2. The program based on quota balance (based on (quota - filled) in @t1)
3. If the program is out of quota, the system will select another program randomly till successful. Another word, FIFO
4. If all program and all quota is filled, tell user 'Sorry, there's no program available at this moment'
I can't imagine How my SQL statement looks like.
I hope someone can help me to imagine the logic and built the T-SQL
Really need help

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

You haven't described what goes into MatrixNo.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Qira SSCommitted Points: 1930 More actions · Answer 2

Kindest Mr Jeff Moden,

Sorry for the weaknesses. I try to re-explain as above

My table and data as following,

declare @t1 table

(idx int identity, programID int, quota int, dtVersion timestamp);

/*programID is unique*/

insert into @t1(programID, quota) values(1, 100);

insert into @t1(programID, quota) values(3, 70);

insert into @t1(programID, quota) values(4, 100);

--and so on

declare @tResult table

(idx int identity, matrixNo int, programID int);

/*Combination of matrixNo and programID will generate a unique row

programID is foreign key to @t1(programID)*/

insert into @tResult(matrixNo, programID) values(29,1);

insert into @tResult(matrixNo, programID) values(38,1);

insert into @tResult(matrixNo, programID) values(37,4);

insert into @tResult(matrixNo, programID) values(39,3);

--and so on

--The application result data stored in @tResult

The scenario as following,

1. Student (matrixNo) will apply any Program (programID)

2. The successful of the application result based on quota balance in @t1. Quota balance = (Application result data in @tResult) - quota

3. If the program is out of quota, the system will select another program randomly till successful. Another word, FIFO

4. If all the program and all the quota is filled, there's no chances for student to apply any program

The transaction scenario as following

1. Student with matrixNo=89 apply the programID=4. Let's say, the program still have the quota, so T-SQL execution as following

insert into @tResult(matrixNo) values(89,4);

2. Student with matrixNo=74 apply the programID=3. Let's say, the program is out the quota, so T-SQL have to choose another programID. May be, T-SQL execution as following

insert into @tResult(matrixNo) values(74,1);

-- the programID choose randomly. Old programID=3. New programID=1

3. Student with matrixNo=102 apply the programID=1. Let's say, all the program and all the quota is FILLED. There's no T-SQL will be executed in @tResult. At this level, message will prompt to user 'Sorry, there's no program available at this moment'

I can't imagine How my SQL statement looks like.

I hope someone can help me to imagine the logic and built the T-SQL

Really need help

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

BWAAA-HAAA!!! Oh, not to worry... I got all the rest of it. 🙂 I just wanted to know what the "MatrixNo" was. Basically, it's just the "StudentID"? Correct?

The only other question that I have is... is this stored proc for a "front end" where only one student request for a program will be submitted at a time?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Qira SSCommitted Points: 1930 More actions · Answer 4

Jeff Moden (10/30/2011)
BWAAA-HAAA!!! Oh, not to worry... I got all the rest of it. 🙂 I just wanted to know what the "MatrixNo" was. Basically, it's just the "StudentID"? Correct?
The only other question that I have is... is this stored proc for a "front end" where only one student request for a program will be submitted at a time?

1. Yes. MatrixNo consider as StudentID

2. Yes. Only one student request for a program will be submitted at a time. But, at another place may be a lot of student request for a program at the same time