Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Show a Start and End Sequence for a Given anchor value Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 3:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, October 6, 2014 5:34 PM
Points: 633, Visits: 369
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(
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!
Post #1457629
Posted Wednesday, May 29, 2013 3:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555

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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1457638
Posted Wednesday, May 29, 2013 4:34 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, October 6, 2014 5:34 PM
Points: 633, Visits: 369
Thanks Mark! That was super quick on the uptake ++

Works perfectly too, based on my example. Much appreciated :)
Post #1457654
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse