February 12, 2013 at 12:31 pm
Hello Everyone
I need Query Help
create table #temp
(cStudentID varchar(09),
cGradeCode varchar(02),
iSchoolYearCode int)
insert into #temp values ('003582450','04',2003)
insert into #temp values ('003582450','05',2004)
insert into #temp values ('003582450','06',2005)
insert into #temp values ('003582450','07',2006)
insert into #temp values ('003582450','08',2007)
insert into #temp values ('003582450','09',2008)
insert into #temp values ('003582450','10',2009)
insert into #temp values ('003582450','11',2010)
insert into #temp values ('003582450','12',2011)
insert into #temp values ('003828573','05',2005)
insert into #temp values ('003828573','10',2008)
insert into #temp values ('003828573','11',2009)
insert into #temp values ('003828573','12',2010)
insert into #temp values ('003844573','11',2008)
insert into #temp values ('003844573','12',2009)
insert into #temp values ('004144573','12',2010)
I need to display Initial Graduation Year based on students cGradeCode and iSchoolYearCode
so if Student initial Grade is '09' the SchoolYearCode = iSchoolYearCode + 4
if Student initial Grade is '10' the SchoolYearCode = iSchoolYearCode + 3
if Student initial Grade is '11' the SchoolYearCode = iSchoolYearCode + 2
if Student initial Grade is '12' the SchoolYearCode = iSchoolYearCode + 1
I try this
select cStudentID,
cGradeCode,
iSchoolYearCode,
CASE WHEN cGradeCode in ('09','10','11','12')
THEN
(select min(iSchoolYearCode) as iSchoolYearCode
from #temp
where cStudentID = a.cStudentID
and cGradeCode = '09') + 4
WHEN cGradeCode in ('10','11','12')
THEN
(select min(iSchoolYearCode) as iSchoolYearCode
from #temp
where cStudentID = a.cStudentID
and cGradeCode = '10') + 3
ELSE '' END AS InitialGradeYear
from #temp a
order by cStudentID,iSchoolYearCode
but it give me the output as below,
cStudentIDcGradeCodeiSchoolYearCodeInitialGradeYear
0035824500420030
0035824500520040
0035824500620050
0035824500720060
0035824500820070
0035824500920082012
0035824501020092012
0035824501120102012
0035824501220112012
0038285730520050
003828573102008NULL
003828573112009NULL
003828573122010NULL
003844573112008NULL
003844573122009NULL
004144573122010NULL
but expected out put is
cStudentIDcGradeCodeiSchoolYearCodeInitialGradeYear
0035824500420030
0035824500520040
0035824500620050
0035824500720060
0035824500820070
0035824500920082012
0035824501020092012
0035824501120102012
0035824501220112012
0038285730520050
0038285731020082011
0038285731120092011
0038285731220102011
0038445731120082011
0038445731220092011
0041445731220102011
February 12, 2013 at 2:08 pm
This matches your description of your business rules but it doesn't match the data you said you wanted. Not sure which one is correct because you said
so if Student initial Grade is '09' the SchoolYearCode = iSchoolYearCode + 4
if Student initial Grade is '10' the SchoolYearCode = iSchoolYearCode + 3
if Student initial Grade is '11' the SchoolYearCode = iSchoolYearCode + 2
if Student initial Grade is '12' the SchoolYearCode = iSchoolYearCode + 1
But in your desired output it doesn't match that business rule.
SELECT cStudentID
,cGradeCode
,iSchoolYearCode
,CASE
WHEN cGradeCode IN ('09', '10', '11', '12')
THEN iSchoolYearCode + 1
ELSE 0
END AS InitialGradeYear
FROM #temp a
ORDER BY cStudentID, iSchoolYearCode
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 2:13 pm
Sean Lange (2/12/2013)
This matches your description of your business rules but it doesn't match the data you said you wanted. Not sure which one is correct because you saidso if Student initial Grade is '09' the SchoolYearCode = iSchoolYearCode + 4
if Student initial Grade is '10' the SchoolYearCode = iSchoolYearCode + 3
if Student initial Grade is '11' the SchoolYearCode = iSchoolYearCode + 2
if Student initial Grade is '12' the SchoolYearCode = iSchoolYearCode + 1
But in your desired output it doesn't match that business rule.
SELECT cStudentID
,cGradeCode
,iSchoolYearCode
,CASE
WHEN cGradeCode IN ('09', '10', '11', '12')
THEN iSchoolYearCode + 1
ELSE 0
END AS InitialGradeYear
FROM #temp a
ORDER BY cStudentID, iSchoolYearCode
Sorry I forget to mention
if below condition matches for Initial Grade, then SchoolYearCode remain same for next years also.
if Student initial Grade is '09' the SchoolYearCode = iSchoolYearCode + 4
if Student initial Grade is '10' the SchoolYearCode = iSchoolYearCode + 3
if Student initial Grade is '11' the SchoolYearCode = iSchoolYearCode + 2
if Student initial Grade is '12' the SchoolYearCode = iSchoolYearCode + 1
for Example student has Grade 09,10,11 then min of grade is 09 so SchoolYearCode = iSchoolYearCode + 4 and remain same for another year also it doesn't change
February 12, 2013 at 2:29 pm
This closer maybe...
SELECT cStudentID
,cGradeCode
,a.iSchoolYearCode
, case when cGradeCode in ('09', '10', '11', '12')
then x.iSchoolYearCode
else 0
end
FROM #temp a
cross apply (select max(iSchoolYearCode) + 1 as iSchoolYearCode from #temp where cStudentID = a.cStudentID and cast(cGradeCode as int) >= 9) x
ORDER BY cStudentID, a.iSchoolYearCode
Your description just doesn't seem to match up with your posted desired output. There is some sort of business rule that I don't understand here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply