June 15, 2012 at 1:09 am
dwain.c (6/15/2012)
Jeez Kingston, you've let the cat out of the bag!!Actually you don't need to do a JOIN of any sort.
Yes, the OP's statement actually made us feel unhelpfull
So, I thought of giving what he probably wants
One more thing is that the correlated sub-query does use a JOIN
You can check the actual execution plan to verify this
You might be knowing this already, but just in case you didn't
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 1:12 am
Kingston Dhasian (6/15/2012)
dwain.c (6/15/2012)
Jeez Kingston, you've let the cat out of the bag!!Actually you don't need to do a JOIN of any sort.
Yes, the OP's statement actually made us feel unhelpfull
So, I thought of giving what he probably wants
One more thing is that the correlated sub-query does use a JOIN
You can check the actual execution plan to verify this
You might be knowing this already, but just in case you didn't
Yeah I knew that. But for some reason, I like the subquery in this case because it looks a little cleaner.
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 1:18 am
dwain.c (6/15/2012)
Kingston Dhasian (6/15/2012)
dwain.c (6/15/2012)
Jeez Kingston, you've let the cat out of the bag!!Actually you don't need to do a JOIN of any sort.
Yes, the OP's statement actually made us feel unhelpfull
So, I thought of giving what he probably wants
One more thing is that the correlated sub-query does use a JOIN
You can check the actual execution plan to verify this
You might be knowing this already, but just in case you didn't
Yeah I knew that. But for some reason, I like the subquery in this case because it looks a little cleaner.
Yes. Individual choices.
Even I have a strange affection towards ROW_NUMBER() and CTE unless they are detrimental to performance, don't know why 🙂
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 1:19 am
Okay, here is my solution:
create table dbo.student (
studentID varchar(12),
Firstname varchar(12),
LastName varchar(12)
);
insert into dbo.student values ('003412581','steve','marnet');
insert into dbo.student values ('004123698','Nice','castello');
create table dbo.school (
scode int,
schoolname varchar(12)
);
insert into dbo.school values (132,'Bankville');
insert into dbo.school values (214,'Scottownship');
insert into dbo.school values (289,'monroville');
insert into dbo.school values (336,'PPS');
create table dbo.studentSchoolHistory (studentID varchar(12),scode int, iSchoolYearcode int);
insert into dbo.studentSchoolHistory values ('003412581',132, 2009);
insert into dbo.studentSchoolHistory values ('003412581',289, 2010);
insert into dbo.studentSchoolHistory values ('003412581',336, 2011);
insert into dbo.studentSchoolHistory values ('004123698',336, 2011);
insert into dbo.studentSchoolHistory values ('004123698',336, 2010);
insert into dbo.studentSchoolHistory values ('004123698',132, 2009);
go -- batch separator
select
ssh.studentID,
ssh.scode,
ssh.iSchoolYearcode,
case when ssh1.studentID is null then ''
when ssh.scode = ssh1.scode then ''
else '1'
end MobilityIndicator
from
dbo.studentSchoolHistory ssh
left outer join dbo.studentSchoolHistory ssh1
on (ssh.studentID = ssh1.studentID
and ssh.iSchoolYearcode = ssh1.iSchoolYearcode + 1)
order by
ssh.studentID,
ssh.scode,
ssh.iSchoolYearcode
go
drop table dbo.student;
drop table dbo.school;
drop table dbo.studentSchoolHistory;
go -- batch separator
June 15, 2012 at 1:23 am
dwain.c (6/15/2012)
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.
Not running your code, but what do you return if the mobility indicator is not 1? The OP indicated that it should be blank.
Plus, I find the corelated subquery to be unnecessary and it could be a performance killer depending on how it is written.
June 15, 2012 at 1:23 am
Kingston Dhasian (6/15/2012)
dwain.c (6/15/2012)
Kingston Dhasian (6/15/2012)
dwain.c (6/15/2012)
Jeez Kingston, you've let the cat out of the bag!!Actually you don't need to do a JOIN of any sort.
Yes, the OP's statement actually made us feel unhelpfull
So, I thought of giving what he probably wants
One more thing is that the correlated sub-query does use a JOIN
You can check the actual execution plan to verify this
You might be knowing this already, but just in case you didn't
Yeah I knew that. But for some reason, I like the subquery in this case because it looks a little cleaner.
Yes. Individual choices.
Even I have a strange affection towards ROW_NUMBER() and CTE unless they are detrimental to performance, don't know why 🙂
My affections come and go. My latest is with the code structure I posted here:
http://www.sqlservercentral.com/Forums/Topic1315948-391-1.aspx#bm1316345
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 1:27 am
Lynn Pettis (6/15/2012)
Not running your code, but what do you return if the mobility indicator is not 1? The OP indicated that it should be blank.
Plus, I find the corelated subquery to be unnecessary and it could be a performance killer depending on how it is written.
Didn't notice the part about returning a blank. That would require wrapping my subquery in a ISNULL and now its getting ugler.
Regarding performance, I'd say (in the immortal words of an MVP who will remain nameless) "it depends." I've seen cases of the correlated subquery beat the crap out of a JOIN when I was not expecting it. Other times it loses. Guess it does depend on how it's written.
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 1:29 am
dwain.c (6/15/2012)
Lynn Pettis (6/15/2012)
Not running your code, but what do you return if the mobility indicator is not 1? The OP indicated that it should be blank.
Plus, I find the corelated subquery to be unnecessary and it could be a performance killer depending on how it is written.
Didn't notice the part about returning a blank. That would require wrapping my subquery in a ISNULL and now its getting ugler.
Regarding performance, I'd say (in the immortal words of an MVP who will remain nameless) "it depends." I've seen cases of the correlated subquery beat the crap out of a JOIN when I was not expecting it. Other times it loses. Guess it does depend on how it's written.
Yes, it does. That's why I said could be a performance killer.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply