need help on SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need help on SQL

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-31 : 01:27:50

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, studentID int, programID int);
/*Combination of studentID and programID will generate a unique row
programID is foreign key to @t1(programID)*/
insert into @tResult(studentID, programID) values(29,1);
insert into @tResult(studentID, programID) values(38,1);
insert into @tResult(studentID, programID) values(37,4);
insert into @tResult(studentID, programID) values(39,3);
--and so on
--The application result data stored in @tResult

The scenario as following,
1. Student (studentID) 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 studentID=89 apply the programID=4. Let's say, the program still have the quota, so T-SQL execution as following

insert into @tResult(studentID, programID) values(89,4);

2. Student with studentID=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@tResult(studentID, programID) values(74,1); -- the programID choose randomly. Old programID=3. New programID=1

3. Student with studentID=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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 04:46:31

something like


create proc assignprogram
@studentId int,
@programid int
as
declare @progquota int,@allotedcnt int,@nextprogram int

select @progquota =quota
from @t1 
where programID=@programid


select @allotedcnt=count(*) 
from @tResult 
where programID=@programid

if @allotedcnt < @progquota 
 insert into @tResult (studentID, programID) values(@studentId,@programid)
else
 begin
  select top 1 @nextprogram=  t.programID
  from @t1 t
  cross apply (select count(*) as cnt
               from  @tResult 
               where programID = t.programID
               )t1
  where cnt < t.quota
  order by t.programID
  if @nextprogram is not null
     insert into @tResult (studentID, programID) values(@studentId,@nextprogram)
  else
     raiserror ('no program available at this moment',16,1)
 end
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-10-31 : 06:00:53

tq sir

your answer is my inspiration

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 06:09:14

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources