Querying Recursive CTE results

Question

Post reply

Querying Recursive CTE results

jenise.marcus

Newbie

Points: 7
More actions
February 26, 2009 at 6:01 pm

#128586

I have a recursive query that gives me the management hierarchy, but now I need to retrieve the $ associated to each individual within that hiearchy and roll them up to the manager. I currently have my CTE in a stored procedure and will be accessing it using ASP.net. How do I "query" the results so I can roll up the dollars?
thanks,
Jenise

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Please see the article at the link in my signature below for better answers quicker. 😉

--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)

noeld SSC Guru Points: 96590 More actions · Answer 2

Please, post sample code and sample data.

You will probably need a level and a path to run your rollups but we can't go further without more info.

Hopefully you understand why Jeff Pointed you to the link on his signature.

* Noel

jenise.marcus Newbie Points: 7 More actions · Answer 3

Here is my stored procedure code:

WITH EmployeeCTE

AS

(

SELECT

UID, firstname, lastname,

1 AS Level,

CONVERT(VARBINARY(MAX), UID) AS thePath

FROM tblUsers

WHERE MID = 604

UNION ALL

SELECT

E.UID, e.firstname, e.lastname,

x.Level + 1 AS Level,

x.thePath + CONVERT(VARBINARY(MAX), E.UID) AS thePath

FROM tblUsers E

JOIN EmployeeCTE x ON x.UID = E.MID

)

SELECT employeeCTE.UID, (employeeCTE.firstname + ' ' + employeeCTE.lastname) AS FullName, Level, thepath,

(SELECT SUM(d.Target) FROM dbo.tblDetails AS d INNER JOIN

dbo.tblHeader AS h ON d.HID = h.HID INNER JOIN dbo.tblIndiv_Visibility AS iv ON h.SAID = iv.SAID

WHERE (iv.UID = employeeCTE.UID) AND (d.PlanYear = 2009)) AS Target,

(SELECT SUM(d.Target) FROM dbo.tblDetails AS d INNER JOIN

dbo.tblHeader AS h ON d.HID = h.HID INNER JOIN dbo.tblIndiv_Visibility AS iv ON h.SAID = iv.SAID,

(Select UID

WHERE (iv.UID = employeeCTE.UID) AND (d.PlanYear = 2010) AND and (Level =1)) AS Future_Target,

FROM EmployeeCTE LEFT OUTER JOIN dbo.tblIndiv_Visibility ON dbo.tblIndiv_Visibility.UID = EmployeeCTE.UID LEFT OUTER JOIN

dbo.tblHeader ON dbo.tblIndiv_Visibility.SAID = dbo.tblHeader.SAID LEFT OUTER JOIN dbo.tblDetails ON dbo.tblHeader.HID = dbo.tblDetails.HID

group by employeeCTE.UID, employeeCTE.firstname + ' ' + employeeCTE.lastname, level, thepath

ORDER BY employeeCTE.thepath

And here are some sample results:

UIDNameLevelthePathTargetFuture_Target

10Bob 10x0000000ANULLNULL

5John20x0000000A0000008BNULLNULL

666Mary30x0000000A0000008B0000029A81210

7Sue30x0000000A0000008B0000118284700

23Jane30x0000000A0000008B00001424NULLNULL

56Bill20x0000000A00000C09NULLNULL

78Larry30x0000000A00000C090000032A71500

90Jason30x0000000A00000C09000010CE42450

99Grant30x0000000A00000C0900001544NULLNULL

34Barry30x0000000A00000C0900001555NULLNULL

2Tom20x0000000A000013D1NULLNULL

1Jen20x0000000A00001408NULLNULL

77Brian10x00000138NULLNULL

54alex20x000001380000004C33700

76Mike20x000001380000004D99840

89Drew20x000001380000029419300

Thank You!

Jenise

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Because the query produces a downline and you're trying to calculate the upline targets, you will need a separate correlated subquery UPDATE (maybe a CROSS-APPLY but haven't tried it) to rollup the target amounts.

--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)