SELECT		c.Cst_key AS Child,
		dbo.return_org_parent(c.cst_key, 0, x.Number) AS Parent
FROM		co_customer AS c WITH (NOLOCK)
INNER JOIN	master..spt_values AS x ON x.Type = 'p'
WHERE		c.cst_type = 'Organization'
		AND c.cst_delete_flag = 0
		AND x.Number BETWEEN 1 AND 7
		AND dbo.return_org_parent(c.cst_key, 0, x.Number) IS NOT NULL

quote:

---

Originally posted by crugerenator

select
parent,
(select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id,
(select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp,
(select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets,
sum(own_assets) as Total_child_own_assets
from
(
Select parent,
Child,
(select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets
from
(Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,1) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,1) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,2) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,2) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,3) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,3) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,4) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,4) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,5) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,5) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,6) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,6) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,7) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,7) is not null )as c
) as d
group by parent
having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)

---

SELECT		q.Parent,
		w.cst_ID AS cst_ID,
		w.cst_Name_Cp AS cst_Name_Cp,
		SUM(ISNULL(y2.org_total_assets_ext, 0)) AS Parent_Total_assets,
		SUM(ISNULL(y1.org_own_assets_ext, 0)) AS Total_child_own_assets
FROM		(
			SELECT		c.Cst_Key AS Child,
					dbo.Return_Org_Parent(c.cst_Key, 0, x.Number) AS Parent
			FROM		co_Customer AS c WITH (NOLOCK)
			INNER JOIN	master..spt_values AS x ON x.Type = 'p'
			WHERE		c.cst_Type = 'Organization'
					AND c.cst_Delete_Flag = 0
					AND x.Number BETWEEN 1 AND 7
		) AS q
LEFT JOIN	dbo.co_organization_ext AS y1 ON y1.org_cst_key_ext = q.Child
LEFT JOIN	dbo.co_organization_ext AS y2 ON y2.org_cst_key_ext = q.Parent
LEFT JOIN	co_Customer AS w WITH (NOLOCK) ON w.cst_Key = q.Parent
WHERE		q.Parent IS NOT NULL
GROUP BY	q.Parent,
		w.cst_ID,
		w.cst_Name_Cp
HAVING		SUM(ISNULL(y1.org_own_assets_ext, 0)) <> SUM(ISNULL(y2.org_total_assets_ext, 0))