August 27, 2009 at 12:12 pm
So from what it sounds like, the UniqueID and Level values are arbitrary numbers that need to be incremental and as long as they appear in the right order, all is well?
Try this:
;WITH DirectReports (UniqueID, CurrentProcessStepID, NextProcessStepID, [Name])
AS
(
SELECTROW_NUMBER() OVER(ORDER BY tp.TransactionID) as UniqueID,
tp.CurrentProcessStepID,
tp.NextProcessStepID,
Name
FROM itTransactionProcess AS tp
LEFT OUTER JOIN dbo.itProcessSteps ON tp.CurrentProcessStepID = dbo.itProcessSteps.ProcessStepID
WHERE LotNumber = @LotNumber
)
SELECT MIN(d1.UniqueID) as UniqueID,
d1.CurrentProcessStepID,
d1.Name,
MIN(d1.UniqueID) as Level
FROM DirectReports d1
LEFT JOIN DirectReports d2
ON d1.NextProcessStepID = d2.CurrentProcessStepID
GROUP BY d1.CurrentProcessStepID, d1.Name
ORDER BY 1
August 27, 2009 at 12:31 pm
Spoke to soon.
There is one minor issue. Once again the UniqueID in not important just that it be unique and the Level is linked to the Parent UniqueID with the first line having level = null
Correct results
11ReceivingNULL
102Sort1 (Sub Group Of Line 1)
833Recycling Storage10 (Sub Group Of Line 2)
10020Custom Sort10 (Sub Group Of Line 2)
8088Refining100 (Sub Group Of Line 4)
648824Custom Shred808 (Sub Group Of Line 5)
Incorrect Results
11ReceivingNULL
22Sort2 (Should be 1)
53Recycling Storage5 (Should be 2)
720Custom Sort7 (Should be 2)
588Refining58 (Should be 7)
10924Custom Shred109 (Should be 58)
August 27, 2009 at 2:29 pm
OK, how 'bout this one:
;WITH DirectReports (UniqueID, CurrentProcessStepID, NextProcessStepID, [Name])
AS
(
SELECTROW_NUMBER() OVER(ORDER BY tp.TransactionID) as UniqueID,
tp.CurrentProcessStepID,
tp.NextProcessStepID,
Name
FROM itTransactionProcess AS tp
LEFT OUTER JOIN dbo.itProcessSteps ON tp.CurrentProcessStepID = dbo.itProcessSteps.ProcessStepID
WHERE LotNumber = @LotNumber
)
SELECT MIN(d1.UniqueID) as UniqueID,
d1.CurrentProcessStepID,
d1.Name,
(SELECT MIN(CurrentProcessStepID) FROM DirectReports WHERE NextProcessStepID = d1.CurrentProcessStepID) as Level
FROM DirectReports d1
LEFT JOIN DirectReports d2
ON d1.NextProcessStepID = d2.CurrentProcessStepID
GROUP BY d1.CurrentProcessStepID, d1.Name
ORDER BY 1
August 27, 2009 at 2:43 pm
Close but your using the CurrentProcessStepID as the level guage. There may be more then one CurrentProcessStep of Sort (2) so this would not allow the higharchy to match up correctly. If you could get the Level to equal the parent records UniqueID that would finish this puppy off.
Thanks so much.
August 27, 2009 at 2:49 pm
August 27, 2009 at 3:02 pm
Hope this helps. The indents are where they are supposed to be indented. I will attach some sample data for you to use shortly.
Unique ID CurrentProcessStepID Name Level
1 1 Receiving NULL
10 2 Sort 1 (Sub Group Of Line 1)
83 3 Recycling Storage 10 (Sub Group Of Line 2)
100 20 Custom Sort 10 (Sub Group Of Line 2)
101 3 Recycling Storage 100 (Sub Group Of Line 2)
808 8 Refining 100 (Sub Group Of Line 4)
6488 24 Custom Shred 808 (Sub Group Of Line 5)
6489 3 Recycling Storage 6488 (Sub Group Of Line 2)
Sample Data
LotNumber = 29291
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply