SQL Script problem

[Freehansje]

I have this table

CREATE TABLE table1(
Proc VARCHAR(10),
PrevProc VARCHAR(10))

Both fields are mandatory. In this table sequences are stored of jobs to run. PrevProc will hold the name of the job which has to finish before the job in Proc can run. What I need is a resultset per sequence, which start with a jobname in PrevProc not existing in Proc(this is the first job of this specific sequence) and next all following jobnames in 12 row:

Job1, Job2, Job3, Job4 etc.

I have been fiddling with a SP, where I have played with temptables and such, but what I get is far from complete and very messy. I wonder if this should be possible to create in a view, but sofar I doubt that.

Any hints are welcome


Kind regards,
FreeHansje

 

[Freehansje]

Hi ESquared,
I have an (imperfect) script to get all the sequences from a given set and I did some tests. I entered your example in the table, which stores the dependencies of each cluster. I then ran my script and received all sequences. I put these sequences in an Excelsheet and grouped all sequences together and checked for the problems you adressed; they don't occur. I think the representation in a posting here is not good enough to show graphically the final result.
If only I could attach this spreadsheet here, then I could show you this final result!
There is a potential other problem, for which I have to consult the designer. Imagine 2 sequences:
Cluster_01, Cluster_02, Cluster_4
And
Cluster_01, Cluster_04

As you see Cluster_04 is 2 times dependent on Cluster_01, once directly and once indirectly. This will pose problems in the FlexGrid, don't know if this may occur.
If you are interested I can post the script and the test dataset I used. I think I can produce the sequences now, although I am going to do some more testing.
All that's left is translating this to VB..
No, don't ask, you don't want to know!

 

[ESquared]

Why do you need to display it in the flexgrid?

It may not be possible after all.

Send me your Excel workbook at (moc tod liamtoh ta egamlaprov) so I can see it, please?

There was a mistake in my diagram. The CI_18 in the middle on the left should have been CI_14. Maybe you figured that out.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]