Query Help

Question

Post reply

Query Help

yogi123

SSCrazy

Points: 2932
More actions
November 25, 2013 at 8:40 am

#282843

Hello
I need one help to develop logic which i can use in my script,
CREATE TABLE #TEMP
(cStudentID VARCHAR(09),
School_Year INT,
CourseID VARCHAR(10),
TermCode VARCHAR(05),
Mark VARCHAR(02))
INSERT INTO #TEMP VALUES ('003804186',2013,'007501','Q1','B')
INSERT INTO #TEMP VALUES ('003804186',2013,'007503','Q1','B')
INSERT INTO #TEMP VALUES ('003804186',2013,'504712','FE1','D')
INSERT INTO #TEMP VALUES ('003804186',2013,'504712','FG1','C')
INSERT INTO #TEMP VALUES ('003804186',2013,'504712','Q1','B')
I get below output
cStudentIDSchool_YearCourseIDTermCodeMark
0038041862013007501Q1B
0038041862013007503Q1B
0038041862013504712FE1D
0038041862013504712FG1C
0038041862013504712Q1B
The requirement is, If there is FG1 TermCode for same Student and Course Then Select Mark for FG1 TermCode Otherwise select Q1 TermCode.
meaning if FG1 THEN FG1
else Q1 and Filter other termcode
see below the desired output is,
cStudentIDSchool_YearCourseIDTermCodeMark
0038041862013007501Q1B
0038041862013007503Q1B
0038041862013504712FG1C
Thanks for Your Help.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Something like this?

WITH CTE AS(

SELECT *,

RANK() OVER(PARTITION BY cStudentID, CourseID ORDER BY CASE Termcode WHEN 'FG1' THEN 1 WHEN 'Q1' THEN 2 ELSE 3 END) rn

FROM #TEMP

)

SELECT *

FROM CTE

WHERE rn = 1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 2

great job posting the DDL;

in this case, i think using ROW_NUMBER(), and a custom CASE expression to control the special order might work well:

SELECT * FROM

(

SELECT row_number() over(partition by cStudentID,School_Year,CourseID

ORDER BY CASE

WHEN TermCode='FG1'

THEN 1

WHEN TermCode='Q1'

THEN 2

ELSE 3

END) AS RW,*

FROM #TEMP

) MyAlias

WHERE

RW = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogi123 SSCrazy Points: 2932 More actions · Answer 3

Awesome

Thanks You so much. It works Perfect for me...

Rahul Bhosale SSC Eights! Points: 931 More actions · Answer 4

Here is i think what you are looking for..

select distinct

a.cStudentID,

a.School_Year,

a.CourseID,

case when b.termcode is not null then b.termcode else a.termcode end as 'termcode' ,

case when b.termcode is not null then b.mark else a.mark end as 'mark'

from

#temp a left outer join

(

select

b.*

from #temp a inner join #temp b

on a.cStudentID = b.cStudentID and a.School_Year = b.School_Year and a.CourseID = b.CourseID

where a.TermCode = 'Q1' and b.termcode = 'FG1'

)

b on a.cStudentID = b.cStudentID and a.CourseID = b.CourseID

For the articles and blogs about SQL please visit... SQL Concepts, scripts and much more

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Are you really suggesting to read the table 3 times instead of once?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Rahul Bhosale SSC Eights! Points: 931 More actions · Answer 6

just a first thought...the solution you given looks perfect...