CTE Bottoms-up.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CTE Bottoms-up....

Author

Topic

djseng
Starting Member

25 Posts

Posted - 2006-08-22 : 16:55:53

Hello all,

I have this hierarchical structure where the data should be calculated from the children up to the parent. It's been a long day of coding and recursion was never really my strong point, so if anyone could help out i would be very grateful. So basically with the sample data below, the children have defined calculated weights, a defined weight which is it's weight on its parent, and the mapped weight which is the percentage of 100 percent of itself... i think i'm close, but i'm pushing 36 hours mark. i keep getting stuck cuz i can't do group by's in the recursive statement, and my brain is no longer working.

-dave


create table dbo.elements (
   parentelementid varchar(32), 
   elementid varchar(32),
   definedweight smallmoney,
   mappedweight smallmoney,
   calculatedweight smallmoney
)

insert table dbo.elements (NULL, 'qa_result', 100.00, NULL, NULL)
insert table dbo.elements ('customer_service', 'call_control', 30.00, 67.00, 20.10)
insert table dbo.elements ('customer_service', 'empathy_and_etiquette', 35.00, NULL, NULL)
insert table dbo.elements ('customer_service', 'soft_skills', 35.00, NULL, NULL)
insert table dbo.elements ('empathy_and_etiquette', 'confidence', 35.00, 0.00, 0.00)
insert table dbo.elements ('empathy_and_etiquette', 'professionalism', 35.00, 33.00, 11.55)
insert table dbo.elements ('empathy_and_etiquette', 'sensitivity', 30.00, 67.00, 20.10)
insert table dbo.elements ('greeting_and_closing', 'closing', 25.00, 100.00, 25.00)
insert table dbo.elements ('greeting_and_closing', 'greeting', 75.00, 0.00, 0.00)
insert table dbo.elements ('process_adherence', 'greeting_and_closing', 20.00, NULL, NULL)
insert table dbo.elements ('process_adherence', 'qualification', 40.00, 100.00, 40.00)
insert table dbo.elements ('process_adherence', 'reservation_recap', 40.00, 100.00, 40.00)
insert table dbo.elements ('qa_result', 'customer_service', 20.00, NULL, NULL)
insert table dbo.elements ('qa_result', 'process_adherence', 25.00, NULL, NULL)
insert table dbo.elements ('qa_result', 'sales_ownership', 30.00, NULL, NULL)
insert table dbo.elements ('qa_result', 'technical_accuracy', 25.00, NULL, NULL)
insert table dbo.elements ('rai_processing', 'call_notes', 25.00, 100.00, 25.00)
insert table dbo.elements ('rai_processing', 'rai_database_fields', 75.00, 0.00, 0.00)
insert table dbo.elements ('rcc_processing', 'rcc_database_fields', 75.00, 0.00, 0.00)
insert table dbo.elements ('rcc_processing', 'reservation_notes', 25.00, 100.00, 25.00)
insert table dbo.elements ('sales_ownership', 'creativity_and_strategy', 35.00, 100.00, 35.00)
insert table dbo.elements ('sales_ownership', 'explore_and_elaborate', 35.00, 100.00, 35.00)
insert table dbo.elements ('sales_ownership', 'product_positioning', 30.00, 100.00, 30.00)
insert table dbo.elements ('soft_skills', 'attitude', 75.00, 100.00, 75.00)
insert table dbo.elements ('soft_skills', 'communication', 25.00, 100.00, 25.00)
insert table dbo.elements ('technical_accuracy', 'offer_details', 25.00, 100.00, 25.00)
insert table dbo.elements ('technical_accuracy', 'rai_processing', 30.00, NULL, NULL)
insert table dbo.elements ('technical_accuracy', 'rcc_processing', 45.00, NULL, NULL)

djseng
Starting Member

25 Posts

Posted - 2006-08-22 : 18:37:11

I... think... i got it... can anyone comply?


WITH Calc (
	ElementId, 
	DefinedWeight, 
	MappedWeight, 
	CalculatedWeight, 
	ParentElementId
) as (
	SELECT
		ElementId,
		DefinedWeight,
		MappedWeight,
		CalculatedWeight,
		ParentElementId
	FROM dbo.Elements
	WHERE CalculatedWeight IS NOT NULL
	UNION ALL
	SELECT
		dw.ElementId,
		dw.DefinedWeight,
		Calc.CalculatedWeight,
		dw.DefinedWeight * (Calc.CalculatedWeight / 100),
		dw.ParentElementId
	FROM dbo.Elements dw
	INNER JOIN Calc
		ON Calc.ParentElementId = dw.ElementId
)
SELECT
	ElementId,
	DefinedWeight,
	SUM(MappedWeight) as MappedWeight,
	SUM(CalculatedWeight) as CalculatedWeight
FROM Calc
GROUP BY
	ElementId,
	DefinedWeight

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-22 : 19:16:12

You didn't provide the expected result set given your sample data, so there really wasn't a way for us to help. Glad you found a solution though!

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources