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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Merge Juniors Data and display under each manager
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohit3907
Starting Member

9 Posts

Posted - 07/17/2015 :  23:51:59  Show Profile  Reply with Quote
i have a table sales with column

CREATE TABLE [dbo].[sales] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[employeeid] INT NULL,
[status] VARCHAR (20) NULL,
[quantity] INT NULL,
[commisiononone] FLOAT (53) NULL,
[acceptedquantity] INT NULL,
[statusdate] DATE NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

I have another table that store employee detail along with its promoting manager

CREATE TABLE [dbo].[employee] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[name] VARCHAR (20) NULL,
[managerid] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

Insert into sales(1,1,'Cancel',200,10,150,'2015-07-21')
Insert into sales(2,1,'Accept',100,20,50,'2015-06-02')
Insert into sales(3,2,'Accept',10,10,10,'2015-05-17')
Insert into sales(4,3,'Accept',30,10,5,'2015-06-06')
Insert into sales(5,3,'Cancel',30,10,5,'2015-06-06')

Insert into employee(1,xyz,Null)
Insert into employee(2,abc,1)
Insert into employee(3,zxc,2)
Insert into employee(4,zvcs,1)
Insert into employee(5,fgs,3)

Now i am trying to generate a report where for each manager i will have employees where managerid will be that particular manager and if there are juniors of the employees the each employee detail will be sum(junior and employee quantity), sum(junior and employee submittedquantity),sum(junior and employee commision(commision is calculated by multiplying commisiononone and acceptedquantity and where status=Accept))

if i put employee id 1 in where clause i want the employees where managerid=1 for example:

ID Name QuantitySubmitted QuantityAccepted Commision
2 abc 70 30 150
4 zvcx 0 0 0

Also this should be filtered using status date.
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000