June 14, 2012 at 8:54 pm
hello friends
i am struck in one problem.
in my problem , i need to display Mobility Indicator.
If SchoolCode in year 2009 and 2010 remain same , then Mobility indicator is blank
If SchoolCode in Year 2009 and 2010 different, then Mobility Indicator display 1 in year 2010.
create table student (studentID varchar(12), Firstname varchar(12), LastName varchar(12))
insert into student values ('003412581','steve','marnet')
insert into student values ('004123698','Nice','castello')
create table school (scode int, schoolname varchar(12))
insert into school values (132,'Bankville')
insert into school values (214,'Scottownship')
insert into school values (289,'monroville')
insert into school values (336,'PPS')
create table studentSchoolHistory (studentID varchar(12),scode int, iSchoolYearcode int)
insert into studentSchoolHistory values ('003412581',132, 2009)
insert into studentSchoolHistory values ('003412581',289, 2010)
insert into studentSchoolHistory values ('003412581',336, 2011)
insert into studentSchoolHistory values ('004123698',336, 2011)
insert into studentSchoolHistory values ('004123698',336, 2010)
insert into studentSchoolHistory values ('004123698',132, 2009)
--Desired Output should be look like
studentID scode iSchoolYearcode Mobility Indicator
003412581 132 2009
003412581 289 2010 1
003412581 336 2011 1
004123698 132 2009
004123698 336 2010 1
004123698 336 2011
so please help me for this
Thanks
June 14, 2012 at 9:09 pm
It appears that there is more than one person stumped on the same homework problem.
http://qa.sqlservercentral.com/Forums/Topic1316131-391-1.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 14, 2012 at 9:22 pm
dwain.c (6/14/2012)
It appears that there is more than one person stumped on the same homework problem.http://qa.sqlservercentral.com/Forums/Topic1316131-391-1.aspx
Thanks for reply. but please help me.
i need help, thats why i post
June 14, 2012 at 10:50 pm
INNER JOIN student with studentSchoolHistory tables to get the first 3 columns. Use a correlated subquery to get the fourth (Mobility Indicator) column.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 12:11 am
surma.sql (6/14/2012)
dwain.c (6/14/2012)
It appears that there is more than one person stumped on the same homework problem.http://qa.sqlservercentral.com/Forums/Topic1316131-391-1.aspx
Thanks for reply. but please help me.
i need help, thats why i post
I have the answer, but before I post it, you need to show us some work. It does you no good if we just give you the answer. In fact, if I were your instructor and found out that you got the answer off a forum, I'd fail you for the assignment and would consider reporting you for academic dishonesty.
You see, I have an ethical reason not to post the answer.
You have been given a suggestion, some guidance. Trying, and post back what you have. We are more than happy to help you if you are doing the work rather than relying on us to do it for you.
June 15, 2012 at 12:12 am
dwain.c (6/14/2012)
INNER JOIN student with studentSchoolHistory tables to get the first 3 columns. Use a correlated subquery to get the fourth (Mobility Indicator) column.
Oh, actually a left outer join and a case statement work quite nicely.
June 15, 2012 at 12:15 am
Lynn Pettis (6/15/2012)
dwain.c (6/14/2012)
INNER JOIN student with studentSchoolHistory tables to get the first 3 columns. Use a correlated subquery to get the fourth (Mobility Indicator) column.Oh, actually a left outer join and a case statement work quite nicely.
There's always more than one way to skin a cat! 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 12:15 am
surma.sql (6/14/2012)
dwain.c (6/14/2012)
It appears that there is more than one person stumped on the same homework problem.http://qa.sqlservercentral.com/Forums/Topic1316131-391-1.aspx
Thanks for reply. but please help me.
i need help, thats why i post
People here are ready to help but not to spoon feed because spoon feeding doesn't help you in the longer run
Moreover, its strange how 2 different people are stuck with the same problem unless you are both of them
If you are stuck in some home work problem, include some attempts you had made to solve the problem
This will give us some idea about your willingness to learn instead of simply asking for ready made solutions
If all you want is ready made solution, here it is( but i am not sure how much you will learn from it )
; WITH cte_studentHistory AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY studentID ORDER BY iSchoolYearcode ) RN, *
FROM studentSchoolHistory
)
SELECT H1.studentID, H1.scode, H1.iSchoolYearcode, CASE WHEN H2.RN IS NULL OR H1.scode = H2.scode THEN '' ELSE '1' END MobilityIndicator
FROM cte_studentHistory H1
LEFT JOIN cte_studentHistory H2 ON H1.studentID = H2.studentID AND H1.RN = H2.RN + 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 15, 2012 at 12:20 am
Kingston Dhasian (6/15/2012)
surma.sql (6/14/2012)
dwain.c (6/14/2012)
It appears that there is more than one person stumped on the same homework problem.http://qa.sqlservercentral.com/Forums/Topic1316131-391-1.aspx
Thanks for reply. but please help me.
i need help, thats why i post
People here are ready to help but not to spoon feed because spoon feeding doesn't help you in the longer run
Moreover, its strange how 2 different people are stuck with the same problem unless you are both of them
If you are stuck in some home work problem, include some attempts you had made to solve the problem
This will give us some idea about your willingness to learn instead of simply asking for ready made solutions
If all you want is ready made solution, here it is( but i am not sure how much you will learn from it )
; WITH cte_studentHistory AS(
SELECT ROW_NUMBER() OVER ( PARTITION BY studentID ORDER BY iSchoolYearcode ) RN, *
FROM studentSchoolHistory
)
SELECT H1.studentID, H1.scode, H1.iSchoolYearcode, CASE WHEN H2.RN IS NULL OR H1.scode = H2.scode THEN '' ELSE '1' END MobilityIndicator
FROM cte_studentHistory H1
LEFT JOIN cte_studentHistory H2 ON H1.studentID = H2.studentID AND H1.RN = H2.RN + 1
Don't even need a CTE to do it. One table, a left outer join, and a case statement.
June 15, 2012 at 12:28 am
Lynn Pettis (6/15/2012)
Kingston Dhasian (6/15/2012)
surma.sql (6/14/2012)
dwain.c (6/14/2012)
It appears that there is more than one person stumped on the same homework problem.http://qa.sqlservercentral.com/Forums/Topic1316131-391-1.aspx
Thanks for reply. but please help me.
i need help, thats why i post
People here are ready to help but not to spoon feed because spoon feeding doesn't help you in the longer run
Moreover, its strange how 2 different people are stuck with the same problem unless you are both of them
If you are stuck in some home work problem, include some attempts you had made to solve the problem
This will give us some idea about your willingness to learn instead of simply asking for ready made solutions
If all you want is ready made solution, here it is( but i am not sure how much you will learn from it )
; WITH cte_studentHistory AS(
SELECT ROW_NUMBER() OVER ( PARTITION BY studentID ORDER BY iSchoolYearcode ) RN, *
FROM studentSchoolHistory
)
SELECT H1.studentID, H1.scode, H1.iSchoolYearcode, CASE WHEN H2.RN IS NULL OR H1.scode = H2.scode THEN '' ELSE '1' END MobilityIndicator
FROM cte_studentHistory H1
LEFT JOIN cte_studentHistory H2 ON H1.studentID = H2.studentID AND H1.RN = H2.RN + 1
Don't even need a CTE to do it. One table, a left outer join, and a case statement.
Yes. Even a simple LEFT JOIN with the same table would do it for the sample data given.
I made the CTE so that even if there are gaps in the years, the solution would work
Handling gaps in years will be just a bit tougher using a simple LEFT JOIN
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 15, 2012 at 12:31 am
Jeez Kingston, you've let the cat out of the bag!!
Actually you don't need to do a JOIN of any sort.
create table #studentSchoolHistory (studentID varchar(12),scode int, iSchoolYearcode int)
insert into #studentSchoolHistory values ('003412581',132, 2009)
insert into #studentSchoolHistory values ('003412581',289, 2010)
insert into #studentSchoolHistory values ('003412581',336, 2011)
insert into #studentSchoolHistory values ('004123698',336, 2011)
insert into #studentSchoolHistory values ('004123698',336, 2010)
insert into #studentSchoolHistory values ('004123698',132, 2009)
SELECT studentID, scode, iSchoolYearcode
,[Mobility Indicator]=(
SELECT 1
FROM #studentSchoolHistory s2
WHERE s1.studentID = s2.studentID AND s1.scode <> s2.scode AND
s1.iSchoolYearcode - 1 = s2.iSchoolYearcode)
FROM #studentSchoolHistory s1
ORDER BY studentID, iSchoolYearcode, scode
And I guess the student and school tables are just noise.
And if you need to handle gaps in years, you can just use Kingston's CTE and replace #studentSchoolHistory in both places in the query above and it will also work.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 12:32 am
You can do it as Follows:
--Creating Tables
create table student (studentID varchar(12), Firstname varchar(12), LastName varchar(12))
create table school (scode int, schoolname varchar(12))
create table studentSchoolHistory (studentID varchar(12),scode int, iSchoolYearcode int)
--Inserting Sample Data
insert into student values ('003412581','steve','marnet')
insert into student values ('004123698','Nice','castello')
insert into school values (132,'Bankville')
insert into school values (214,'Scottownship')
insert into school values (289,'monroville')
insert into school values (336,'PPS')
insert into studentSchoolHistory values ('003412581',132, 2009)
insert into studentSchoolHistory values ('003412581',289, 2010)
insert into studentSchoolHistory values ('003412581',336, 2011)
insert into studentSchoolHistory values ('004123698',336, 2011)
insert into studentSchoolHistory values ('004123698',336, 2010)
insert into studentSchoolHistory values ('004123698',132, 2009)
--Query For You Requirement
Select b.studentID, b.scode, b.iSchoolYearcode,
(Case When b.scode <> a.scode Then 1 Else '' End) As Mobility_Indicator
From
(Select *, ROW_NUMBER() Over (Partition By StudentId Order By scode) As rn From studentSchoolHistory) AS a
Right JOIN
(Select *, ROW_NUMBER() Over (Partition By StudentId Order By scode) As rn From studentSchoolHistory) AS b
ON a.studentID = b.studentID AND b.rn = (a.rn + 1)
June 15, 2012 at 12:32 am
Kingston Dhasian (6/15/2012)
Lynn Pettis (6/15/2012)
Kingston Dhasian (6/15/2012)
surma.sql (6/14/2012)
dwain.c (6/14/2012)
It appears that there is more than one person stumped on the same homework problem.http://qa.sqlservercentral.com/Forums/Topic1316131-391-1.aspx
Thanks for reply. but please help me.
i need help, thats why i post
People here are ready to help but not to spoon feed because spoon feeding doesn't help you in the longer run
Moreover, its strange how 2 different people are stuck with the same problem unless you are both of them
If you are stuck in some home work problem, include some attempts you had made to solve the problem
This will give us some idea about your willingness to learn instead of simply asking for ready made solutions
If all you want is ready made solution, here it is( but i am not sure how much you will learn from it )
; WITH cte_studentHistory AS(
SELECT ROW_NUMBER() OVER ( PARTITION BY studentID ORDER BY iSchoolYearcode ) RN, *
FROM studentSchoolHistory
)
SELECT H1.studentID, H1.scode, H1.iSchoolYearcode, CASE WHEN H2.RN IS NULL OR H1.scode = H2.scode THEN '' ELSE '1' END MobilityIndicator
FROM cte_studentHistory H1
LEFT JOIN cte_studentHistory H2 ON H1.studentID = H2.studentID AND H1.RN = H2.RN + 1
Don't even need a CTE to do it. One table, a left outer join, and a case statement.
Yes. Even a simple LEFT JOIN with the same table would do it for the sample data given.
I made the CTE so that even if there are gaps in the years, the solution would work
Handling gaps in years will be just a bit tougher using a simple LEFT JOIN
You are over thinking the homework assignment. Plus, having worked in a public school district, if there was a gap in years, I would assume that the mobility indicator would be blank for the year that a student came back to the district.
June 15, 2012 at 12:40 am
You are over thinking the homework assignment. Plus, having worked in a public school district, if there was a gap in years, I would assume that the mobility indicator would be blank for the year that a student came back to the district.
Maybe, yes. In fact I was even thinking about the performance for some time 😀
I saw your post on LEFT JOIN only after posting my solution, had I seen it earlier I probably wouldn't have posted a similar solution to yours.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 15, 2012 at 12:46 am
Kingston Dhasian (6/15/2012)
You are over thinking the homework assignment. Plus, having worked in a public school district, if there was a gap in years, I would assume that the mobility indicator would be blank for the year that a student came back to the district.
Maybe, yes. In fact I was even thinking about the performance for some time 😀
I saw your post on LEFT JOIN only after posting my solution, had I seen it earlier I probably wouldn't have posted a similar solution to yours.
Yes, something like that happened with me as well. I was doin this solution when some work came up and I had to pause it there and do the other work. The window was open all the time. I came back did the solution and that is when I see all your replies.
Sorry bout that. 🙂
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply