Merge Juniors Data and display under each manager

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)

Merge Juniors Data and display under each manager

Author

Topic

mohit3907
Starting Member

9 Posts

Posted - 2015-07-17 : 23:51:59

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources