Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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

cStudentID	School_Year	CourseID	TermCode	Mark
003804186 2013 007501 Q1 B
003804186 2013 007503 Q1 B
003804186 2013 504712 FE1 D
003804186 2013 504712 FG1 C
003804186 2013 504712 Q1 B


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,

cStudentID School_Year CourseID TermCode Mark
003804186 2013 007501 Q1 B
003804186 2013 007503 Q1 B
003804186 2013 504712 FG1 C


Thanks for Your Help.
Post #1517299
Posted Monday, November 25, 2013 8:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:21 PM
Points: 3,645, Visits: 7,961
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1517310
Posted Monday, November 25, 2013 8:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517311
Posted Monday, November 25, 2013 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
Awesome

Thanks You so much. It works Perfect for me...
Post #1517315
Posted Monday, November 25, 2013 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:22 AM
Points: 148, Visits: 185
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
Post #1517328
Posted Monday, November 25, 2013 9:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:21 PM
Points: 3,645, Visits: 7,961
Are you really suggesting to read the table 3 times instead of once?


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1517343
Posted Monday, November 25, 2013 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:22 AM
Points: 148, Visits: 185
just a first thought...the solution you given looks perfect...
Post #1517396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse