June 4, 2013 at 2:29 pm
Hello
I need one help in developing Query
CREATE TABLE #TEMP
(
cStudentID varchar(09),
iSchoolCode int,
cGradeCode char(02),
dtEntryDate datetime,
dtWithdrawDate datetime,
iSchoolYearCode int
)
INSERT INTO #TEMP VALUES ('001223436',301,'10','2004-09-02 00:00:00.000','2005-06-16 00:00:00.000',2003)
INSERT INTO #TEMP VALUES ('001223436',301,'11','2005-09-01 00:00:00.000','2006-06-15 00:00:00.000',2004)
INSERT INTO #TEMP VALUES ('001223436',301,'12','2006-08-31 00:00:00.000','2007-06-12 00:00:00.000',2005)
INSERT INTO #TEMP VALUES ('001223436',301,'10','2007-08-30 00:00:00.000','2008-06-11 00:00:00.000',2006)
INSERT INTO #TEMP VALUES ('001223436',301,'11','2008-08-28 00:00:00.000','2009-06-12 00:00:00.000',2007)
INSERT INTO #TEMP VALUES ('001223436',301,'12','2009-09-03 00:00:00.000','2010-06-16 00:00:00.000',2008)
INSERT INTO #TEMP VALUES ('001223436',301,'12','2010-09-02 00:00:00.000','2011-06-13 00:00:00.000',2009)
INSERT INTO #TEMP VALUES ('001223436',301,'12','2011-06-14 00:00:00.000','2011-06-15 00:00:00.000',2010)
INSERT INTO #TEMP VALUES ('001223436',301,'99','2011-09-01 00:00:00.000','2012-06-13 00:00:00.000',2011)
If Student GradeCode is repeat in Next School Year, we need to set flag for that.
so Expected result is
StudentIDiSchoolYearCodeIsRepeat
00122343620030
00122343620040
00122343620050
00122343620061
00122343620071
00122343620081
00122343620091
00122343620101
00122343620110
Please help me to develop this.
If Anyone do this w/o CTE will greatly Appreciate because i need to use in one of the Logic in my Script.
Thanks
June 4, 2013 at 2:43 pm
Awesome job posting ddl and sample data!!! This is exactly the right way to post!!!
Here is one way to solve your challenge.
select * ,
case when x.iSchoolYearCode is null then 0 else 1 end as IsRepeat
from #TEMP t
cross apply
(
select MAX(iSchoolYearCode) as iSchoolYearCode
from #TEMP t2
where t2.cStudentID = t.cStudentID
and t2.cGradeCode = t.cGradeCode
and t2.iSchoolYearCode < t.iSchoolYearCode
) x
If you struggle with cross apply you could do this with an inline subquery.
select *
, (select case when MAX(iSchoolYearCode) is null then 0 else 1 end from #TEMP t2 where t2.cStudentID = t.cStudentID and t2.cGradeCode = t.cGradeCode and t2.iSchoolYearCode < t.iSchoolYearCode ) as IsRepeat
from #TEMP t
order by iSchoolYearCode
These are actually the same query under the hood, just a couple different ways of writing the same thing.
_______________________________________________________________
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/
June 4, 2013 at 2:49 pm
Sean Lange (6/4/2013)
Awesome job posting ddl and sample data!!! This is exactly the right way to post!!!Here is one way to solve your challenge.
select * ,
case when x.iSchoolYearCode is null then 0 else 1 end as IsRepeat
from #TEMP t
cross apply
(
select MAX(iSchoolYearCode) as iSchoolYearCode
from #TEMP t2
where t2.cStudentID = t.cStudentID
and t2.cGradeCode = t.cGradeCode
and t2.iSchoolYearCode < t.iSchoolYearCode
) x
If you struggle with cross apply you could do this with an inline subquery.
select *
, (select case when MAX(iSchoolYearCode) is null then 0 else 1 end from #TEMP t2 where t2.cStudentID = t.cStudentID and t2.cGradeCode = t.cGradeCode and t2.iSchoolYearCode < t.iSchoolYearCode ) as IsRepeat
from #TEMP t
order by iSchoolYearCode
These are actually the same query under the hood, just a couple different ways of writing the same thing.
This is work !!!!!!!!!
Wonderful !!!!!
Thank You so Much !!!!
June 4, 2013 at 2:56 pm
You are quite welcome. Glad that worked for you.
_______________________________________________________________
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/
June 4, 2013 at 3:08 pm
Just for the fun of giving more options, eventhough they might not be better than the ones previously posted.
SELECT cStudentID,
iSchoolYearCode,
CASE WHEN ROW_NUMBER() OVER( PARTITION BY cGradeCode ORDER BY iSchoolYearCode ) > 1 THEN 1 ELSE 0 END IsRepeat
FROM #TEMP
ORDER BY iSchoolYearCode
Or a variation from Sean's CROSS APPLY
select * ,
case when x.iSchoolYearCode < t.iSchoolYearCode then 1 else 0 end as IsRepeat
from #TEMP t
cross apply
(
select MIN(iSchoolYearCode) as iSchoolYearCode
from #TEMP t2
where t2.cStudentID = t.cStudentID
and t2.cGradeCode = t.cGradeCode
) x
order by t.iSchoolYearCode
The sample data is really small to check for performance, but I just wanted to give options.
June 4, 2013 at 3:26 pm
One cat, four ways to skin it. You gotta love SSC.
_______________________________________________________________
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/
June 4, 2013 at 9:22 pm
I like Luis's first choice (to avoid the triangular join). Here's another:
SELECT cStudentID, iSchoolYearCode
,IsRepeat=CASE WHEN n<>iSchoolYearCode THEN 1 ELSE 0 END
FROM (
SELECT *
,n=MIN(iSchoolYearCode) OVER (PARTITION BY cStudentID, iSchoolCode, cGradeCode)
FROM #TEMP) a
ORDER BY iSchoolYearCode
So 5 ways to skin that cat (and probably more).
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 5, 2013 at 7:55 am
dwain.c (6/4/2013)
I like Luis's first choice (to avoid the triangular join). Here's another:
SELECT cStudentID, iSchoolYearCode
,IsRepeat=CASE WHEN n<>iSchoolYearCode THEN 1 ELSE 0 END
FROM (
SELECT *
,n=MIN(iSchoolYearCode) OVER (PARTITION BY cStudentID, iSchoolCode, cGradeCode)
FROM #TEMP) a
ORDER BY iSchoolYearCode
So 5 ways to skin that cat (and probably more).
Woohoo!!!
Of course the version I posted isn't really a triangular join because it has an aggregate on the "wide side". Not the most efficient though. I prefer both this version and Luis's first.
_______________________________________________________________
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/
June 5, 2013 at 5:44 pm
Sean Lange (6/5/2013)
dwain.c (6/4/2013)
I like Luis's first choice (to avoid the triangular join). Here's another:
SELECT cStudentID, iSchoolYearCode
,IsRepeat=CASE WHEN n<>iSchoolYearCode THEN 1 ELSE 0 END
FROM (
SELECT *
,n=MIN(iSchoolYearCode) OVER (PARTITION BY cStudentID, iSchoolCode, cGradeCode)
FROM #TEMP) a
ORDER BY iSchoolYearCode
So 5 ways to skin that cat (and probably more).
Woohoo!!!
Of course the version I posted isn't really a triangular join because it has an aggregate on the "wide side". Not the most efficient though. I prefer both this version and Luis's first.
Apologies for taking liberties with Jeff's "triangular join" terminology. You are of course correct that the aggregate probably makes it fall outside of that class.
It's just that I've been pressed for time lately (that's why I haven't posted much) and it was merely serendipitous that I happened to find a thread I thought I might be able contribute to and had the time (even though limited) to do so.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply