Show a Start and End Sequence for a Given anchor value
-
Hello,
I'm having quite sticky problem at the moment and hoping someone can help to educate me!
For the purposes of the example I have a table of data that shows what subjects a student is enrolled in.
The subjects are a series of numbers (This is a varchar in the udnerlying table - please don't bring that up :)).
We have an application which has been built which consumes data from a SQL VIEW and treats the data differently if the student belongs in a sequential range of subjects (1,2,3..n+1).
Please note, this must be a SELECT statement from a view. We can't create tables, execute stored procedures or any dynamic SQL.
I am tearing my hair out tring to get the data out properly within one select statement.
I can create user defined functions. I can use a numbers/tally table.
Basically the table is [student], [subject#]:
-- create dummy table
CREATE TABLE #SeqStudentSubj(
Studentsvarchar(25),
Subjectsvarchar(10)
)
-- insert some data
INSERT #SeqStudentSubj (Students, subjects) VALUES ('Johnathan', '233')
INSERT #SeqStudentSubj (Students, subjects) VALUES ('Johnathan', '234')
INSERT #SeqStudentSubj (Students, subjects) VALUES ('Johnathan', '235')
INSERT #SeqStudentSubj (Students, subjects) VALUES ('Johnathan', '467')
INSERT #SeqStudentSubj (Students, subjects) VALUES ('Johnathan', '469')
INSERT #SeqStudentSubj (Students, subjects) VALUES ('Johnathan', '470')
INSERT #SeqStudentSubj (Students, subjects) VALUES ('Johnathan', '322')
The data needs to be pivoted and grouped into any sequences. The above inserts should be presented as:
Name, IsASequence, SeqStart, SeqEnd
Johnathan, 'Y', 233, 235
Johnathan, 'N', 467, 467
Johnathan, 'Y', 469, 470
Johnathan, 'N', 322, 322
As this is SQL 2000 I have no CTE, I have no OVER (Order BY ..), or partion on...
So far from my peers I've been told: "Good Luck! Upgrade!"
Thanks in advance helpful, fantastic people!
-
Very inefficient but gives correct results
SELECT Students AS Name,CASE WHEN COUNT(*) > 1 THEN 'Y' ELSE 'N' END AS IsASequence,
MIN(CAST(Subjects AS INT)) AS SeqStart,
MAX(CAST(Subjects AS INT)) AS SeqEnd
FROM (
SELECT t1.Students,
t1.Subjects,
CAST(t1.Subjects AS INT) - (SELECT COUNT(*) FROM #SeqStudentSubj t2
WHERE t2.Students=t1.Students AND CAST(t2.Subjects AS INT) <= CAST(t1.Subjects AS INT)) AS rn
FROM #SeqStudentSubj t1) x
GROUP BY Students,rn
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark! That was super quick on the uptake ++
Works perfectly too, based on my example. Much appreciated 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply