Parent - child and roll 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 2005 Forums

Transact-SQL (2005)

Parent - child and roll up

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-07-21 : 06:09:56

Dear Good people of sqlteam

I have table of employees and column called managedby.
I want to roll sales up from employee to lead to manager to director and etc.

create table
(
empid int
,Name varchar(255)
,sales decimal(38,2)
,managedby int
)

Thanks in advance.

I sign for fame not for shame but all the same, I sign my name.

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-21 : 06:36:11

Have a look - http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=RecursiveCTE&referringTitle=Home

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 06:46:57

And watch out for circular reference!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-07-21 : 07:04:54

Thanks all..

Thanks Nageswar9, yes I want that BUT with roll up functionalities..

You omitted sales column. I want the column summed up to the immediate Manager.

Thanks

I sign for fame not for shame but all the same, I sign my name.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-07-21 : 12:45:28

Hi All,

Kindly find work done so far.. I just need to do the roll up on actual and target columns.

Thanks all....

quote:

with emp(Period,Label,[name],Unit,Department,[Group],Division,Managed_By,Manager,Actual,Target) as
(
select
a.DW_Time_key Period
,Label
,b.name
,b.Unit
,b.Department
,b.[Group]
,b.Division
,b.Managed_By
,b.Manager
,sum(a.Actual) Actual
,sum(a.Budget) Target
from
KPI_EDW.dbo.Fact_ASO_SCORECARD a
,KPI_EDW.dbo.DIM_Employee b
where
a.DW_EMPLOYEE_KEY = b.DW_EMPLOYEE_KEY
and DW_KPI_KEY = 1008
and DW_Time_key = 200906
--and DW_Division_KEY = 1003
group by
a.DW_Time_key
,b.name
,b.Unit
,b.Department
,b.[Group]
,b.Division
,b.Managed_By
,b.Manager
,b.Label
)
,CTE (Label,[Name],[Manager], Managed_By,Actual,Target,[level]) as
(
select cast(Label as int) Label,[Name],[Manager], null,Actual,Target,0
from emp
where cast(Label as int) = '582'

union all

select
cast(h2.Label as int) Label
,h2.[Name]
,h2.[Manager]
,cast(h2.Managed_By as int) Managed_By
,h2.Actual
,h2.Target
,[level] + 1
from emp h2
inner join cte
on cast(h2.Managed_By as int) = cast(cte.Label as int)
)

select *
from cte
order by [level]

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources