Sum Junior sales and get data for senior

Transact-SQL
,
1

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.

2

Not clear if you are looking for SSRS help, SQL help or both. If you need SQL help, please post the query you are working on.

3

This is what i worked on so far:

    CREATE PROCEDURE [dbo].[PositionReport]
    @from varchar(10),
    @to varchar(10),
    @title int,
    @id int
AS
    with commision1(id,commision)
  as (select e.id
            ,sum(isnull(s.commision ,0))
        from dbo.employee as e
             left outer join dbo.sales as s
                          on s.employeeid=e.id where s.status='Accept' and s.statusdate>=@from and s.statusdate<=@to
       group by e.id
     )
    ,commision2(id,managerid,commision,extracommision)
  as (select e.id
            ,p.id
            ,c.commision
            ,cast(0 as float)
            
        from dbo.employee as e
             left outer join dbo.employee as p
                          on p.id=e.managerid
             left outer join commision1 as c
                          on c.id=e.id
       where c.commision>0
      union all
      select p.id
            ,p.managerid
            ,cast(0 as float)
            ,ec.commision+ec.extracommision
            
        from commision2 as ec
             inner join dbo.employee as e
                     on e.id=ec.id
             inner join dbo.employee as p
                     on p.id=ec.managerid
       where ec.commision+ec.extracommision>0
     )
select a.id, a.name
      ,isnull(sum(commision),0) as commision
      ,isnull(sum(extracommision),0) as extracommision,(select count(*) from sales cap where a.id=cap.employeeid and status='Accept') as Accepted,(select count(*) from sales cap where a.id=cap.agentid and status='Pending') as Pending,(select count(*) from sales cap where a.id=cap.employeeid and status='Cancelled') as Cancelled
      ,(select isnull(sum(cap.quantity),0) from sales cap where a.id=cap.agentid) as QuantitySubmitted,(select isnull(sum(cap.acceptedquantity),0) from sales cap where a.id=cap.employeeid )as QuantityAccepted
  from commision2 c full outer join employee a on a.id=c.id where a.managerid=@id
 group by a.id,a.name,c.id,a.managerid
 order by a.id

RETURN 0