| Author |
 Topic  |
|
|
lcsgeek
Starting Member
USA
38 Posts |
 Posted - 02/10/2013 : 10:34:44
|
This query (Fall Scores):
SELECT
studentID as fSid
, TestRITScore as fScore
FROM
tblTestScores
WHERE
(TermName = 'Fall 2012')
AND (GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')
ORDER BY
studentID
Returns this data (Fall Scores):
fSid fScore
118 220
1201481 204
1201535 210
1201850 200
1599 208
164 215
1921 212
195 194
196 203
197 195
200 201
210 202
229 200
239 230
243 200
245 216
256 201
298 232
340 198
384 189
406 200
48 188
525 181
57 191
62 200
89 214
This query (Winter Scores):
SELECT
studentID as wSid
, TestRITScore as wScore
FROM
tblTestScores
WHERE
(TermName = 'Winter 2013')
AND (GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')
ORDER BY
studentID
Returns this data (Winter Scores):
wSid wScore
118 224
1201481 203
1201535 207
1201850 213
1201933 217
1201963 191
1201964 194
1599 228
164 218
1921 223
196 209
197 205
200 209
210 211
229 213
239 231
243 199
245 210
256 204
298 232
340 200
384 189
48 203
525 189
57 199
62 202
89 214
This query (Combined Fall and Winter scores):
SELECT
tFall.studentId as fSid
, tFall.TestRITScore as fScore
, tWinter.wSid
, tWinter.wScore
FROM
tblTestScores tFall
FULL OUTER JOIN
(SELECT
studentId as wSid
, TestRITScore as wScore
FROM
tblTestScores
WHERE
(TermName = 'Winter 2013')
AND (GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')
) tWinter ON tFall.StudentID = tWinter.wSid
WHERE
(TermName = 'Fall 2012')
AND (GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')
ORDER BY
tFall.StudentName
Returns this data (Combined Fall and Winter scores):
fSid fScore wSid wScore
1599 208 1599 228
48 188 48 203
57 191 57 199
62 200 62 202
1921 212 1921 223
89 214 89 214
1201850 200 1201850 213
118 220 118 224
164 215 164 218
195 194 NULL NULL
196 203 196 209
197 195 197 205
200 201 200 209
210 202 210 211
229 200 229 213
1201481 204 1201481 203
239 230 239 231
243 200 243 199
245 216 245 210
256 201 256 204
298 232 298 232
340 198 340 200
1201535 210 1201535 207
384 189 384 189
406 200 NULL NULL
525 181 525 189 |
|
|
lcsgeek
Starting Member
USA
38 Posts |
Posted - 02/10/2013 : 10:42:19
|
| If you look at the Winter Scores result set you'll see a studentId of 1201963 with a score of 191 and studentId of 1201964 with a score of 194 which don't appear on the combined query. I want all fall and winter records to be combined in the combined query and it looks like its only returning NULLs on one side of the query. Can anyone provide direction? |
 |
|
|
lcsgeek
Starting Member
USA
38 Posts |
Posted - 02/10/2013 : 10:55:09
|
| I know that the combined WHERE (TermName = 'Fall 2012') is causing my problem but I'm stumped on how to include the winter scores. Obviously WHERE (TermName = 'Fall 2012' OR TermName = 'Winter 2013') would return multiple records but I want them combined in a single record with NULLs on both sides. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/10/2013 : 11:22:34
|
If there is only one score per student id in each of the queries (i.e, each Term), you can use an aggregate function (such as MAX) as shown below. If you have more than one score for a given student ID, then there has to be more information on how to present the results SELECT
studentID as fSid
, MAX(CASE WHEN TermName = 'Fall 2012' THEN TestRITScore END) AS fScore
, MAX(CASE WHEN TermName = 'Winter 2013' THEN TestRITScore END) AS wScore
FROM
tblTestScores
WHERE
(GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')
GROUP BY
studentID
ORDER BY
studentID
It lookslike studentID is always numeric, but you have the data type as varchar. If it is guaranteed that the student id will always be numeric, it would be better to make the datatype numeric as well. |
|
|
|
lcsgeek
Starting Member
USA
38 Posts |
Posted - 02/10/2013 : 11:37:13
|
Hi James,
Thanks as always for your contributions. As it turns out in this case I have historical information in my database from terms and years gone past and your query, by removing the term from the where clause returns all historical 4th grade scores - with NULLs of course but there's no way to distinguish between current and historical 4th graders. |
Edited by - lcsgeek on 02/10/2013 11:38:23 |
|
|
|
lcsgeek
Starting Member
USA
38 Posts |
Posted - 02/10/2013 : 11:49:06
|
This did it:
SELECT
tFall.fSid
, tFall.fStu
, tFall.fScore
, tWinter.wSid
, tWinter.wStu
, tWinter.wScore
FROM
(SELECT
termName as fTerm
, studentId as fSid
, studentName as fStu
, TestRITScore as fScore
FROM
tblTestScores
WHERE
(TermName = 'Fall 2012')
AND (GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')
) tFall
FULL OUTER JOIN
(SELECT
termName as wTerm
, studentId as wSid
, studentName as wStu
, TestRITScore as wScore
FROM
tblTestScores
WHERE
(TermName = 'Winter 2013')
AND (GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')
) tWinter ON tFall.fSid = tWinter.wSid
ORDER BY
tFall.fStu
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/10/2013 : 12:08:14
|
Glad you got it working!
The query I posted would not work correctly if you had other semesters also. But, you can add a where clause to the query I posted to include both semesters: WHERE
TermName in ('Fall 2012','Winter 2013')
AND (GradeName = '4')
AND (MeasurementScaleName = 'Mathematics')
AND (RIGHT(TestTypeName,5) = 'Goals')But what you posted is good as well. The reason I am proposing mine is because that requires only one pass through the table or index. |
|
|
|
lcsgeek
Starting Member
USA
38 Posts |
Posted - 02/10/2013 : 12:52:46
|
James,
I really appreiciate your insight. I don't know what motivates you to get your head around someone elses trouble but I for one really appreicate it. Your solutions are clean and concise as well. Thanks again. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/10/2013 : 17:13:09
|
Its no trouble lcs; I enjoy staring at SQL code. I enjoy everything about SQL and programming. Yeah, I know that sounds really geekish :)
On top of that, the company I am working for is going out of business, so the work load is very light. The major effort at this point is putting on a tie and suit and going to job interviews. So this is one way to keep myself busy.
Regardless, thank you for your kindness in saying it. |
|
|
| |
Topic |
|
Full Outer Join
