August 8, 2016 at 8:10 am
sku370870 (8/8/2016)
drew.allen (8/5/2016)
sku370870 (8/5/2016)
drew.allen (8/5/2016)
sku370870 (8/5/2016)
Thanks for your reply. But when I am on any particular stage, all I have to hand is the StakeholderID for that stage - so I can't start with the original stakeholder - I have to 'look up the table' to find the first stakeholder. I was hoping there was some way of doing the cte where you could go find the Stakeholders going up and then down - as sorting them would not be a problem as I have a DateAdded field I can sort on (as Stages may not be sequential as they can be added out of order by users if they need to do the same stage twice).Original stakeholders have StakeholderWasID = 0.
Drew
Yes, but if I have a StakeholderID of, say, 4 - I don't know which StakeholderID that has a StakeholderWasID of zero is the 'parent' of StakeholderID 4 - without working up the table to find out.
Look, I know it can be done, because I wrote a query that does it. I'm trying to show you the path to take to get there, without handing it to you on a platter, but you refuse to even consider what I've said.
Drew
I'm sorry, but I don't understand how to establish which is the parent with a StakeholderWasID of zero - without using the cte that I posted. If you do, I'd be very pleased to see it.
This may not be how Jeff would solve this problem, but I came up with something that solves the problem based on your sample data.
declare @TargetStakeHolder int;
set @TargetStakeHolder = 4;
with Stakeholders1 as (
select
StakeholderID,
StakeholderWasID
from
#tblStakeHolders
where
StakeholderID = @TargetStakeHolder
union all
select
tsh.StakeholderID,
tsh.StakeholderWasID
from
#tblStakeholders tsh
inner join Stakeholders1 sh1
on (sh1.StakeholderID = tsh.StakeholderWasID)
), Stakeholders2 as (
select
StakeholderID,
StakeholderWasID
from
#tblStakeHolders
where
StakeholderID = @TargetStakeHolder
union all
select
tsh.StakeholderID,
tsh.StakeholderWasID
from
#tblStakeholders tsh
inner join Stakeholders2 sh2
on (sh2.StakeholderWasID = tsh.StakeholderID)
), TargetStakeHolders as (
select StakeholderID from Stakeholders1
union
select StakeholderID from Stakeholders2
)
select
*
from
TargetStakeHolders tsh
inner join #tblStakeholdersNotes tsn
on tsh.StakeholderID = tsn.StakeholderID;
-- OR
with Stakeholders1 as (
select
StakeholderID,
StakeholderWasID
from
#tblStakeHolders
where
StakeholderID = @TargetStakeHolder
union all
select
tsh.StakeholderID,
tsh.StakeholderWasID
from
#tblStakeholders tsh
inner join Stakeholders1 sh1
on (sh1.StakeholderID = tsh.StakeholderWasID)
), Stakeholders2 as (
select
StakeholderID,
StakeholderWasID
from
#tblStakeHolders
where
StakeholderID = @TargetStakeHolder
union all
select
tsh.StakeholderID,
tsh.StakeholderWasID
from
#tblStakeholders tsh
inner join Stakeholders2 sh2
on (sh2.StakeholderWasID = tsh.StakeholderID)
), TargetStakeHolders as (
select StakeholderID from Stakeholders1
union all
select StakeholderID from Stakeholders2
)
select
*
from
#tblStakeholdersNotes tsn
where
exists(select 1 from TargetStakeHolders tsh where tsh.StakeholderID = tsn.StakeholderID);
Personally, I like the second option I posted.
August 8, 2016 at 11:12 am
Here are the two queries that I was alluding to. The first one starts with the original stakeholders and moves down the tree. Then you filter based on records having the same original stakeholder as the record that you are interested in.
;
WITH rCTE(OriginalStakeHolderID, StakeholderID, StageID, StakeholderWasID) AS (
SELECT StakeholderID AS OriginalStakeHolderID, StakeholderID, StageID, StakeholderWasID
FROM #tblStakeholders
WHERE StakeholderWasID = 0
UNION ALL
SELECT c.OriginalStakeHolderID, e.StakeholderID, e.StageID, e.StakeholderWasID
FROM #tblStakeholders e
INNER JOIN rCTE c ON e.StakeholderWasID = c.StakeholderID
)
SELECT *
FROM rCTE
INNER JOIN #tblStakeholdersNotes tsn
ON rCTE.StakeholderID = tsn.StakeholderID
WHERE rCTE.OriginalStakeHolderID IN (
SELECT OriginalStakeHolderID
FROM rCTE AS filter
WHERE filter.StakeholderID = 4
)
;
The second approach, which uses fewer reads when you are looking for a specific stakeholder, but probably uses more reads if you need a group of stakeholders, first moves up the tree to find the original stakeholder for the record of interest, and then moves down the tree from that stakeholder.
;
WITH rCTE_up( StakeholderID, StageID, StakeholderWasID ) AS (
SELECT s.StakeholderID, s.StageID, s.StakeholderWasID
FROM #tblStakeholders s
WHERE s.StakeholderID = 4
UNION ALL
SELECT p.StakeholderID, p.StageID, p.StakeholderWasID
FROM #tblStakeholders p
INNER JOIN rCTE_up AS c
ON p.StakeholderID = c.StakeholderWasID
)
, rCTE_down( StakeholderID, StageID ) AS (
SELECT rCTE_up.StakeholderID, rCTE_up.StageID
FROM rCTE_up
WHERE rCTE_up.StakeholderWasID = 0
UNION ALL
SELECT ts.StakeholderID, ts.StageID
FROM #tblStakeholders ts
INNER JOIN rCTE_down
ON ts.StakeholderWasID = rCTE_down.StakeholderID
)
SELECT *
FROM rCTE_down
INNER JOIN #tblStakeholdersNotes tsn
ON rCTE_down.StakeholderID = tsn.StakeholderID
;
I haven't had a chance to review the other solutions posted to see how they compare.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 8, 2016 at 11:39 am
Okay, after looking at the other code posted, it looks like Lynn's code will work only with paths. My code will work with all directed acyclic graphs. A path has at most one parent and one child, a directed acyclic graph can have any number of parents and children as long as there are no cycles (or loops). The sample data provided shows paths, but it's not clear whether that is a property of the data as a whole or just the subset chosen as the sample.
The reason that you were having problems is that you were treating it as an undirected graph, which means that you were introducing loops. rCTEs have troubles when you introduce loops.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 8, 2016 at 3:28 pm
Hey Jeff, are you going to use your Hierarchies on Steroids approach? 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 8, 2016 at 8:03 pm
drew.allen (8/8/2016)
Here are the two queries that I was alluding to.
Thanks for taking the time to post that, Drew. I appreciate it very much.
Lynn Pettis (8/8/2016)
I came up with something that solves the problem based on your sample data
You too, Lynn. Not many folks post for hierarchical problems.
--Jeff Moden
August 8, 2016 at 8:10 pm
The Dixie Flatline (8/8/2016)
Hey Jeff, are you going to use your Hierarchies on Steroids approach? 🙂
That's what I was thinking. Gotta look at what's been posted a bit more.
--Jeff Moden
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply