SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Show a Start and End Sequence for a Given anchor value


Show a Start and End Sequence for a Given anchor value

Author
Message
vonHarry
vonHarry
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 388
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 Smile).

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(
Students varchar(25),
Subjects varchar(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!
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3812 Visits: 24515
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




vonHarry
vonHarry
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 388
Thanks Mark! That was super quick on the uptake ++

Works perfectly too, based on my example. Much appreciated Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search