CTE Recursive Query
-
I have the following CTE Recursive Query:
With downline (ConsultantID,ConsultantName,SponsorID,SponsorName,DownLineLevel)
AS
(
SELECT A.ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID)
,0 as DownLineLevel
FROM dbo.consultant A
WHERE A.ConsultantID = @ConsultantID
UNION ALL
SELECT A.ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID) As SponsorName
,DownLineLevel + 1
FROM dbo.consultant AS A
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
)
SELECT A.DownLineLevel
-- ,C.BumpUpDate
,A.ConsultantID
,A.ConsultantName
,C.EmailAddress
,D.Title AS AchievedTitle
, A.SponsorID
,A.SponsorName
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
but I am not getting the order I need. What these does is suppose to set the Downlinelevel by precendence, eg:
0
1
1
2
2
3
2
1
But what it is giving me
0
1
1
1
2
2
2
3
3
Any ideas?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD -
Just add the ORDER BY clause to the CTE...
With downline (ConsultantID,ConsultantName,SponsorID,SponsorName,DownLineLevel)
AS
(
SELECT A.ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID)
,0 as DownLineLevel
FROM dbo.consultant A
WHERE A.ConsultantID = @ConsultantID
UNION ALL
SELECT A.ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID) As SponsorName
,DownLineLevel + 1
FROM dbo.consultant AS A
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
)
SELECT A.DownLineLevel
-- ,C.BumpUpDate
,A.ConsultantID
,A.ConsultantName
,C.EmailAddress
,D.Title AS AchievedTitle
, A.SponsorID
,A.SponsorName
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
ORDER BY DownLineLevel
--Ramesh
-
The problem with that is it puts them all in sequencial order which is not right.
the way it works is that a top level consultant (level 0) has subordinate consultants (level 1) and the subordinate, subordinate consultants (level 2) can have consultant under them (Level 3).
So what I need to see is:
Level
0
1
2
2
1
2
3
2
3
1
And not
0
1
1
1
1
2
2
2
2
3
3
3
Thanks
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD -
Try adding a materialised path to your CTE
With downline (ConsultantID,ConsultantName,SponsorID,SponsorName,DownLineLevel,FullPath)
AS
(
SELECT A.ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID)
,0 as DownLineLevel
,CAST(A.ConsultantID AS VARCHAR(MAX))
FROM dbo.consultant A
WHERE A.ConsultantID = @ConsultantID
UNION ALL
SELECT A.ConsultantID
,A.FirstName + ' ' + A.LastName as ConsultantName
,A.SponsorID
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = a.SponsorID) As SponsorName
,DownLineLevel + 1
,B.FullPath + '/' + CAST(A.ConsultantID AS VARCHAR(MAX))
FROM dbo.consultant AS A
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
)
SELECT A.DownLineLevel
-- ,C.BumpUpDate
,A.ConsultantID
,A.ConsultantName
,C.EmailAddress
,D.Title AS AchievedTitle
, A.SponsorID
,A.SponsorName
FROM DownLine AS A
LEFT OUTER JOIN dbo.consultant AS C ON
A.ConsultantID = C.ConsultantID
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
C.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
,C.BumpUpDate
,C.EmailAddress
,D.Title
ORDER BY A.FullPath
____________________________________________________
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
-
This worked like a champ. Thanks a lot.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD -
Problem with such calcs is that you have to recalculate them over and over. Recommend you take a look at Joe Celko's post on the "Nested Set Model" instead of recalculating the "Adjacency Model" over and over.
http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply