July 14, 2009 at 2:19 pm
I'm not sure how well I'll be able to describe this, but here goes. I have a table of self referencing nodes. Some nodes represent tasks in a project and some nodes represent containers to create a project structure. There is also a top level node representing the project itself which has no parent. To come up with billing totals (to be billed and previously billed) I would like to sum an amount column in a different allocation table where the records correspond to task nodes (many allocations to one task node). Some of my container nodes are marked as printable (usually the top level under the project node) and they should be the starting place.
Sorry if my description is confusing. So I think the SQL should look something like this, but I don't know how to complete the references. Please point me in the right direction if you can see where I am going wrong.
WITH cte
AS(
SELECT id
FROM nodes
WHERE id = starting point from list below
UNION ALL
SELECT id
FROM nodes
INNER JOIN cte
ON parent_id = cte.id
)
SELECT id, node_name,
(SELECT SUM(po_amount)
FROM po_allocation
WHERE node_id IN (cte)),
(SELECT SUM(billing_amount)
FROM billing
WHERE node_id IN (cte))
FROM nodes
WHERE project_id = @project_id
AND is_printed = 1
I know I have left out some AS alias stuff, but you'll get the idea. Is this possible? Or is there another way to do it without looping through the records in .NET code. Thanks for taking the time to read this.
Wayne
July 14, 2009 at 2:43 pm
I would separate the hierarchy query into a function, and then use Cross Apply to query it for each node. That's my first instinct anyway.
I can give a more complete answer if you can supply the table definition and a sample insert statement. Ideally, a sample of what you want the output to look like from that sample data, would make it so I could check my results against what you're looking for. Are those things you can supply?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2009 at 2:50 pm
I can supply that info, however I think I may on to something. I am "tagging" the CTE with an extra field that shows what the top container's ID is. That should let me know filter out the node_id's I need by top level parent. Here is what it looks like so far:
DECLARE @project_id numeric(18,0)
SET @project_id = 34
WITH nodeList
AS (
SELECT p1.id, x = id
FROM project_nodes AS p1
WHERE p1.project_id = @project_id
AND p1.is_printed = 1
AND p1.parent_id 0
UNION ALL
SELECT p2.id, x
FROM project_nodes AS p2
INNER JOIN nodeList
ON nodeList.id = p2.parent_id
WHERE p2.project_id = @project_id
)
SELECT name,
(SELECT ISNULL(SUM(amount), 0)
FROM project_po_allocations AS a1
WHERE a1.project_node_id IN
(SELECT id FROM nodeList
WHERE x = p3.id)),
(SELECT ISNULL(SUM(dollars_billing), 0)
FROM project_time_billing AS b1
WHERE b1.node_id IN
(SELECT id FROM nodeList
WHERE x = p3.id))
FROM project_nodes p3
WHERE p3.project_id = @project_id
AND p3.is_printed = 1
AND p3.parent_id 0
I know it's ugly, and I'm not sure if this is the right way to go, but so far I think I'm getting valid numbers. If this doesn't work out, I will supply more info. Thanks for looking over the original statement!
Wayne
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply