need help to build update statement with some conditions

Question

need help to build update statement with some conditions

miss.delinda

SSCommitted

Points: 1616
More actions
April 13, 2012 at 11:47 pm

#254091

I apply for assistance. May there willing to help
I have table's and data as following,
declare @tApplyProgram table (myID varchar(50), programID varchar(10), stSVT char(1) null);
insert into @tApplyProgram(myID,programID) values('1925','172');
insert into @tApplyProgram(myID,programID) values('3512','172');
insert into @tApplyProgram(myID,programID) values('4474','172');
insert into @tApplyProgram(myID,programID) values('1925','184');
/*
@tApplyProgram keep applicant and their programID
myID and programID is unique
*/
declare @tResult table (myID varchar(50), programID varchar(10), subjectCd varchar(50), merit tinyint)
insert into @tResult values('1925','172','35',6);
insert into @tResult values('3512','172','16',8);
insert into @tResult values('3512','172','26',4);
insert into @tResult values('3512','172','40',8);
insert into @tResult values('4474','172','16',16);
insert into @tResult values('4474','172','16',8);
insert into @tResult values('1925','184','16',8);
/*
myID, programID and subjectCd is unique
*/
declare @tRequirement table (programID varchar(10), noOfMinSubject tinyint, minMerit tinyint)
insert into @tRequirement values('172',3,4);
insert into @tRequirement values('184',1,5);
insert into @tRequirement values('227',2,6);
/*
programID is unique
*/
1. Each programID have a
- The minimum number of subjects
- Minimum merit
2. Please look at @tResult where myID='1925' and programID='172'. The results are
- 1925 / 172 have 1 subject taken
3. Please look at @tResult where myID='3512' and programID='172'. The results are
- 3512 / 172 have 3 subjects taken
4. The conditions are
- subjects taken >= @tRequirement(noOfMinSubject)
- @tResult(merit) >= @tRequirement(minMerit)
5. If the conditions is
- PASS, set stSVT=1
- FAILED, set stSVT=0
- else, set stSVT=NULL
Expected result shown below,
myID | programID | stSVT
----------------------------------------
1925172 0 --- noOfMinSubject only 1
3512172 1 --- noOfMinSubject is OK, and all subject's minMerit >= 4
4474176 NULL --- no data for 176 in @tRequirement
1925184 1 --- noOfMinSubject is OK, but there is subject's minMerit not >=5
I need expert help

Viewing 3 posts - 1 through 2 (of 2 total)

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

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 1

The query required is listed below. I have included a query to show the result set but this is not required in the final code.

declare @tApplyProgram table (myID varchar(50), programID varchar(10), stSVT char(1) null);

insert into @tApplyProgram(myID,programID) values('1925','172');

insert into @tApplyProgram(myID,programID) values('3512','172');

insert into @tApplyProgram(myID,programID) values('4474','176');

insert into @tApplyProgram(myID,programID) values('1925','184');

/*

@tApplyProgram keep applicant and their programID

myID and programID is unique

*/

declare @tResult table (myID varchar(50), programID varchar(10), subjectCd varchar(50), merit tinyint)

insert into @tResult values('1925','172','35',6);

insert into @tResult values('3512','172','16',8);

insert into @tResult values('3512','172','26',4);

insert into @tResult values('3512','172','40',8);

insert into @tResult values('4474','172','16',16);

insert into @tResult values('4474','172','16',8);

insert into @tResult values('1925','184','16',8);

/*

myID, programID and subjectCd is unique

*/

declare @tRequirement table (programID varchar(10), noOfMinSubject tinyint, minMerit tinyint)

insert into @tRequirement values('172',3,4);

insert into @tRequirement values('184',1,5);

insert into @tRequirement values('227',2,6);

/*

programID is unique

*/

/*

1. Each programID have a

- The minimum number of subjects

- Minimum merit

2. Please look at @tResult where myID='1925' and programID='172'. The results are

- 1925 / 172 have 1 subject taken

3. Please look at @tResult where myID='3512' and programID='172'. The results are

- 3512 / 172 have 3 subjects taken

4. The conditions are

- subjects taken >= @tRequirement(noOfMinSubject)

- @tResult(merit) >= @tRequirement(minMerit)

5. If the conditions is

- PASS, set stSVT=1

- FAILED, set stSVT=0

- else, set stSVT=NULL

Expected result shown below,

myID | programID | stSVT

----------------------------------------

1925 172 0 --- noOfMinSubject only 1

3512 172 1 --- noOfMinSubject is OK, and all subject's minMerit >= 4

4474 176 NULL --- no data for 176 in @tRequirement

1925 184 0 --- noOfMinSubject is OK, but there is subject's minMerit not >=5

*/

-- Query to return check results (demo only)

select MA.myID, MA.programID,

case when MA.CountSubjects >= RQ.noOfMinSubject and

MA.MinAchievedMerit >= RQ.minMerit then 1 else 0

end as Outcome

from @tRequirement as RQ

inner join

(

select myID, programID, count(Distinct subjectCD) as CountSubjects, min(merit) as MinAchievedMerit

from @tResult

group by myID, programID

) as MA

on RQ.programID = MA.programID

-- Updating

update AP

set stSVT = R.Outcome

from @tApplyProgram as AP

inner join (select MA.myID, MA.programID,

case when MA.CountSubjects >= RQ.noOfMinSubject and

MA.MinAchievedMerit >= RQ.minMerit then 1 else 0

end as Outcome

from @tRequirement as RQ

inner join

(

select myID, programID, count(Distinct subjectCD) as CountSubjects, min(merit) as MinAchievedMerit

from @tResult

group by myID, programID

) as MA

on RQ.programID = MA.programID) as R

on AP.myID = R.myID and AP.programID = R.programID

select *

from @tApplyProgram

Fitz

miss.delinda SSCommitted Points: 1616 More actions · Answer 2

Mark Fitzgerald-331224 (4/14/2012)
The query required is listed below. I have included a query to show the result set but this is not required in the final code.
declare @tApplyProgram table (myID varchar(50), programID varchar(10), stSVT char(1) null);
insert into @tApplyProgram(myID,programID) values('1925','172');
insert into @tApplyProgram(myID,programID) values('3512','172');
insert into @tApplyProgram(myID,programID) values('4474','176');
insert into @tApplyProgram(myID,programID) values('1925','184');
/*
@tApplyProgram keep applicant and their programID
myID and programID is unique
*/
declare @tResult table (myID varchar(50), programID varchar(10), subjectCd varchar(50), merit tinyint)
insert into @tResult values('1925','172','35',6);
insert into @tResult values('3512','172','16',8);
insert into @tResult values('3512','172','26',4);
insert into @tResult values('3512','172','40',8);
insert into @tResult values('4474','172','16',16);
insert into @tResult values('4474','172','16',8);
insert into @tResult values('1925','184','16',8);
/*
myID, programID and subjectCd is unique
*/
declare @tRequirement table (programID varchar(10), noOfMinSubject tinyint, minMerit tinyint)
insert into @tRequirement values('172',3,4);
insert into @tRequirement values('184',1,5);
insert into @tRequirement values('227',2,6);
/*
programID is unique
*/
/*
1. Each programID have a
- The minimum number of subjects
- Minimum merit
2. Please look at @tResult where myID='1925' and programID='172'. The results are
- 1925 / 172 have 1 subject taken
3. Please look at @tResult where myID='3512' and programID='172'. The results are
- 3512 / 172 have 3 subjects taken
4. The conditions are
- subjects taken >= @tRequirement(noOfMinSubject)
- @tResult(merit) >= @tRequirement(minMerit)
5. If the conditions is
- PASS, set stSVT=1
- FAILED, set stSVT=0
- else, set stSVT=NULL
Expected result shown below,
myID | programID | stSVT
----------------------------------------
1925 172 0 --- noOfMinSubject only 1
3512 172 1 --- noOfMinSubject is OK, and all subject's minMerit >= 4
4474 176 NULL --- no data for 176 in @tRequirement
1925 184 0 --- noOfMinSubject is OK, but there is subject's minMerit not >=5
*/
-- Query to return check results (demo only)
select MA.myID, MA.programID,
case when MA.CountSubjects >= RQ.noOfMinSubject and
MA.MinAchievedMerit >= RQ.minMerit then 1 else 0
end as Outcome
from @tRequirement as RQ
inner join
(
select myID, programID, count(Distinct subjectCD) as CountSubjects, min(merit) as MinAchievedMerit
from @tResult
group by myID, programID
) as MA
on RQ.programID = MA.programID
-- Updating
update AP
set stSVT = R.Outcome
from @tApplyProgram as AP
inner join (select MA.myID, MA.programID,
case when MA.CountSubjects >= RQ.noOfMinSubject and
MA.MinAchievedMerit >= RQ.minMerit then 1 else 0
end as Outcome
from @tRequirement as RQ
inner join
(
select myID, programID, count(Distinct subjectCD) as CountSubjects, min(merit) as MinAchievedMerit
from @tResult
group by myID, programID
) as MA
on RQ.programID = MA.programID) as R
on AP.myID = R.myID and AP.programID = R.programID
select *
from @tApplyProgram
Fitz

Tq sir. Your guidance is an inspiration for me