April 14, 2012 at 12:04 pm
I have schema and data as below,
declare @tApplyProgram table (myID varchar(50), programID varchar(10), stTR char(1) null)
insert into @tApplyProgram(myID,programID) values('1925','184');
insert into @tApplyProgram(myID,programID) values('4474','172');
insert into @tApplyProgram(myID,programID) values('8890','172');
insert into @tApplyProgram(myID,programID) values('5578','172');
insert into @tApplyProgram(myID,programID) values('2980','172');
insert into @tApplyProgram(myID,programID) values('2500','172');
insert into @tApplyProgram(myID,programID) values('1925','180');
insert into @tApplyProgram(myID,programID) values('5578','180');
/*
@tApplyProgram keep applicant and their programID
myID and programID is unique
*/
declare @tResult table (myID varchar(50), subjectCd varchar(50), merit tinyint)
insert into @tResult values('1925','01', 19)
insert into @tResult values('1925','02', 12)
insert into @tResult values('1925','03', 11)
insert into @tResult values('4474','03', 18)
insert into @tResult values('4474','04', 19)
insert into @tResult values('4474','05', 17)
insert into @tResult values('4474','06', 11)
insert into @tResult values('5578','01', 13)
insert into @tResult values('5578','02', 15)
insert into @tResult values('5578','03', 18)
insert into @tResult values('2980','01', 14)
insert into @tResult values('2980','02', 15)
/*
@tResult keep their applicant's result
myID and subjectCd is unique
*/
declare @tRulesD table (programID varchar(50), subjectCd varchar(50), merit tinyint)
insert into @tRulesD values('172','03', 12)
insert into @tRulesD values('172','04', 9)
insert into @tRulesD values('172','05', 14)
insert into @tRulesD values('184','01', 10)
insert into @tRulesD values('184','02', 12)
insert into @tRulesD values('184','03', 11)
insert into @tRulesD values('180','03', 12)
/*
@tRulesD keep programID rules and regulation
programID and subjectCd is unique
*/
I need to Update @tApplyProgram set stTR=1
- if @tResult(subjectCd) is match / exceed in @tRulesD(subjectCd)
- AND @tResult(merit) >= @tRulesD(merit)
Else set stTR=0
expected results as follows,
myID | programID | stTR
------------------------------------
1925184 1 /*1925 have rows in @tResult, and 184 is match in @tRulesD. And, all merit > = @tRulesD(merit)*/
4474172 1 /*4474 have rows in @tResult, and 172 is exceed in @tRulesD. And, all merit > = @tRulesD(merit) */
5578172 0 /*5578 have rows in @tResult, and 172 is not match in @tRulesD ~ only subject cd=03 is match*/
2980172 0 /*2980 have rows in @tResult, and 172 is not match in @tRulesD ~ none of subject cd is match*/
1925180 0 /*1925 have rows in @tResult, and 180 is exceed in @tRulesD. But, all merit not > = @tRulesD(merit) ~ 11 not > = 12*/
5578180 1 /*5578 have rows in @tResult, and 180 is exceed in @tRulesD. And, all merit > = @tRulesD(merit) ~ 18 > = 12*/
So far, my update statement as follow,
update @tApplyProgram
set stTR =
(
select
case when max (case when u.subjectcd is null then 1 else 0 end)
+ max (case when r.subjectcd is null then 1 else 0 end)
= 0
then 1
else case when count (u.subjectcd) > 0
and count (r.subjectcd) > 0
then 0
else null
end
end
from (select * from @tResult r where r.myid = [@tApplyProgram].myid) r
full outer join
(select * from @tRulesD u where u.ProgramID = [@tApplyProgram].programid) u
on r.subjectCd = u.subjectCd
)
I appeal for help from experts. I'm really stuck
April 14, 2012 at 2:28 pm
If I understand what you require :
1) work out the number of passes (rules merit <= results merit)
2) work out the number of results possible
3) for each row in the applyprogram whether the passes exceeds the results, if so then 1 else 0
declare @tApplyProgram table (myID varchar(50), programID varchar(10), stTR char(1) null)
insert into @tApplyProgram(myID,programID) values('1925','184');
insert into @tApplyProgram(myID,programID) values('4474','172');
insert into @tApplyProgram(myID,programID) values('8890','172');
insert into @tApplyProgram(myID,programID) values('5578','172');
insert into @tApplyProgram(myID,programID) values('2980','172');
insert into @tApplyProgram(myID,programID) values('2500','172');
insert into @tApplyProgram(myID,programID) values('1925','180');
insert into @tApplyProgram(myID,programID) values('5578','180');
/*
@tApplyProgram keep applicant and their programID
myID and programID is unique
*/
declare @tResult table (myID varchar(50), subjectCd varchar(50), merit tinyint)
insert into @tResult values('1925','01', 19)
insert into @tResult values('1925','02', 12)
insert into @tResult values('1925','03', 11)
insert into @tResult values('4474','03', 18)
insert into @tResult values('4474','04', 19)
insert into @tResult values('4474','05', 17)
insert into @tResult values('4474','06', 11)
insert into @tResult values('5578','01', 13)
insert into @tResult values('5578','02', 15)
insert into @tResult values('5578','03', 18)
insert into @tResult values('2980','01', 14)
insert into @tResult values('2980','02', 15)
/*
@tResult keep their applicant's result
myID and subjectCd is unique
*/
declare @tRulesD table (programID varchar(50), subjectCd varchar(50), merit tinyint)
insert into @tRulesD values('172','03', 12)
insert into @tRulesD values('172','04', 9)
insert into @tRulesD values('172','05', 14)
insert into @tRulesD values('184','01', 10)
insert into @tRulesD values('184','02', 12)
insert into @tRulesD values('184','03', 11)
insert into @tRulesD values('180','03', 12)
/*
@tRulesD keep programID rules and regulation
programID and subjectCd is unique
*/
update A
set stTR = isnull(Passed,0) -- update and set 0 if not matched at all
from @tApplyProgram AS A
left join
(
-- calculate where count passes > required passes
select A1.myID, A1.programID,
case when CountOfPasses >= CountOfRequiredPasses then 1 else 0 end as Passed
from
(
select A.*, B.CountOfRequiredPasses from
(
-- find number of passed gained by myID per program
select A.MyID, A.programID ,count(RU.Merit) as CountOfPasses
from @tApplyProgram as A
inner join @tRulesD as RU
on A.programID = RU.programID
inner join @tResult as RE
on RE.subjectCd = RU.subjectCd
and RE.myID = A.myID
where RU.merit <= RE.merit
group by A.MyID, A.programID
) as A
inner join
(
-- find number of passed required in each program
select RU.programid ,count(RU.Merit) as CountOfRequiredPasses
from @tRulesD as RU
group by RU.programID
) as B
on A.programID = B.programID
) as A1
) as C
on A.myID = C.myID and A.programID = C.programID
select * from @tApplyProgram
Fitz
April 14, 2012 at 9:48 pm
Sir,
It's work. Your guidance is an inspiration for me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply