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

Team query

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-09-20 : 03:10:18

Dear ALL Experts,

I want a query in the following scenario, please help me.

For Example:-

There are four Employee i.e Emp A, Emp B, Emp C, Emp D

now everone have their sales figures i.e

Emp A = 1000
Emp B = 3000
Emp C = 400
Emp D = 5000

Now the Emp C, Emp D is reporting to Emp B, and Emp B is reporting to Emp A.

So when i want total sales figure of Emp B then its should be:- Emp B = Emp B + Emp C + Emp D
when i want total sales figure of Emp A, then its should be :- Emp A= Emp A + Emp B + Emp C + Emp D

I have created a table also, where C and D is reporting to B and B is reporting to A.

Please guide me.

Regards,
AKM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 03:12:50

how is hierarchy set up in table? can you show table structure with some sample data?

if its properly setup you can use a recursive CTE to achieve this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-09-20 : 04:34:37


empid	empname	   managerid	managername
1	emp a		
2	emp b	     1	        emp a
3	emp c	     2	        emp b
4	emp d	     2	        emp b

quote:
Originally posted by visakh16

how is hierarchy set up in table? can you show table structure with some sample data?

if its properly setup you can use a recursive CTE to achieve this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 05:15:31

then its easier

use recursive cte solution given in below link.

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2011-09-20 : 05:43:34

Thanks dear :)

quote:
Originally posted by visakh16

then its easier

use recursive cte solution given in below link.

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 05:46:15

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources