July 8, 2010 at 5:24 am
can any one hep me in this situation...
First i will explain the scenario.
we have a tbale containing the following data....
data is like
studentId, ExamID, StartdtID, Score,AttemptFK
68,3208,20100324,NULL,2170
68,3208,20100324,20,2177
67,3189,20100324,NULL,2167
55,3246,20100324,NULL,2240
66,3232,20100323,NULL,2204
Each student may have attempted an exam more than one time. Student number 68 might have attended an examID 3208, 2 times.
so number of attempts for that student for that examid will be 2.
Now I want to display the Last Score Obtained, the condition here is,
1. If attempted more than 3 times, take the third attempt score, If attempted less than three times take the last attempts score
for the 2nd column, I want to display Maximum Score Achieved,
The condition here is , Last Score Obtained among the 3 attempt3 if it is less than or equals to 3. If it is more than 3, thae third attempt i want.
July 8, 2010 at 6:36 am
Hi,
To get the max score if the attempts or 3 or more, can you run the following query and check it:
select *
from student_mark1 STU_MAIN
where
attemptfk = (select case when (select count(1) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid) = 1
then STU_MAIN.attemptfk
when (select count(1) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid) = 2
then (select min(attemptfk) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid
and nvl(score,0) = (select nvl(max(unique score),0) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid)
)
when (select count(1) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid) >= 3
then (
select min(attemptfk) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid
and nvl(score,0) =
(
select nvl(max(unique score),0) from student_mark1 a_stu
where a_stu.studentid = STU_MAIN.studentid and a_stu.examid = STU_MAIN.examid
and
3 >= (select count(1) from
student_mark1
where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid
and attemptfk <= a_stu.attemptfk
)
)
)
end
from dual
)
;
July 8, 2010 at 8:48 am
First, let's put the test data in a format that can be easily used by people here. If you had done this, other people would have been more willing to help you out. So, HELP US HELP YOU. Read the first link in my signature for how to do this.
Note that for studentID=68, I added two other rows to be able to test the >3 part:
DECLARE @TestData TABLE (studentId int, ExamID int, StartdtID datetime, Score int NULL, AttemptFK int)
INSERT INTO @TestData
SELECT 68,3208,'20100324',NULL,2170 UNION ALL
SELECT 68,3208,'20100324',20,2177 UNION ALL
SELECT 68,3208,'20100325',35,2179 UNION ALL
SELECT 68,3208,'20100326',50,2187 UNION ALL
SELECT 67,3189,'20100324',NULL,2167 UNION ALL
SELECT 55,3246,'20100324',NULL,2240 UNION ALL
SELECT 66,3232,'20100323',NULL,2204
Now, here's the code to produce the results you want.
;WITH CTE AS
(
SELECT studentId,
ExamID,
StartdtID,
Score,
AttemptFK,
-- assign an attempt # to each student/exam combination
RN = ROW_NUMBER() OVER (PARTITION BY studentID, ExamID ORDER BY AttemptFK)
FROM @TestData
), CTE2 AS
(
SELECT studentId,
ExamID,
StartdtID,
Score,
AttemptFK,
-- reverse the ordering, so the last one per student/exam is #1
RN = ROW_NUMBER() OVER (PARTITION BY studentID, ExamID ORDER BY RN DESC)
FROM CTE
-- skip all > 3
WHERE RN < 4
)
-- get the results, showing just for #1. This will be either:
-- 1. if < 4 attempts taken, will be the last test taken.
-- 2. if > 3 attempts taken, will be the score of the third test.
select studentId, ExamID, StartdtID, Score
from CTE2
where RN = 1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2010 at 12:35 am
thanks 🙂 it works
July 9, 2010 at 1:07 am
This should work for the first column requirement:
SELECT *
FROM student_mark1 STU_MAIN
WHERE attemptfk = ( select case when ((select count(1) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid) >= 3)
then (select attemptfk from student_mark1 a_stu
where a_stu.studentid = STU_MAIN.studentid and a_stu.examid = STU_MAIN.examid
and
3 = (select count(1) from
student_mark1
where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid
and attemptfk <= a_stu.attemptfk
))
when ((select count(1) from student_mark1 where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid) = 2)
then (select attemptfk from student_mark1 a_stu
where a_stu.studentid = STU_MAIN.studentid and a_stu.examid = STU_MAIN.examid
and
2 = (select count(1) from
student_mark1
where studentid = STU_MAIN.studentid and examid = STU_MAIN.examid
and attemptfk <= a_stu.attemptfk
))
else STU_MAIN.attemptfk
end
from dual
)
;
July 12, 2010 at 12:46 am
Hi,
Can you check this is working or not? This is Oracle Syntax
select * from
(select a.*, count(*) over (partition by examid) attempt
,row_number( ) over (partition by examid order by studentid,examid,attemptfk) ROW_NUM
from student_mark1 a
)
where row_num = (case when attempt >=3 then 3
when attempt = 2 then 2
else row_num
end);
Venkat Mahesh
July 12, 2010 at 12:47 am
Hi,
Can you check this is working or not? This is Oracle Syntax and should fulfill the second column requirement
select * from
(select a.*, count(*) over (partition by examid) attempt
,row_number( ) over (partition by examid order by studentid,examid,attemptfk) ROW_NUM
from student_mark1 a
)
where row_num = (case when attempt >=3 then 3
when attempt = 2 then 2
else row_num
end);
Venkat Mahesh
July 13, 2010 at 2:18 am
thanks Wayne.... your query helps me alot.
definitly i will follow your suggestions.
thanks once again for a great help.... 🙂
July 13, 2010 at 7:06 am
Wayne, I want the same CTE query for the second thing also.
can you please help me in this regard.
Maximum Score Obtained :
if attempts are less than or equals to 3 then take the max score in those 3 attempts.
If it is more than 3, then take the third attempt.
please help in this regard...
Thanks in advance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply