Recursive SQL query to get amount according to the role

See more:

SQL-Server

MySQL

database

T-SQL

Following are my tables:

    **[UserMaster]**
  UserId Int, UserName Varchar(200),AddedBy Int
    
   UserId     EmpName           AddedBy 
    1          admin             0
    2          SubAdmin1         1
    3          Vikas             2
    4          Mohit             3
    5          Atul              4
    6          Vishal            2
    7          Mani              3
    8          Sunny             1
    
    
[SalesMaster]
SalesId Int, UserId Int (FK_UserMaster_UserId) , Price Int
    
    SalesId  UserId   Price     StatusId**
    
    10        1         100        1 
    11        2         200        1
    12        3         300        1
    13        4         500        1
    14        5         100        2
    15        6         200        1
    16        7         111        2
    17        8         222        1
    18        1          50        1
    19        2          20        1
    
    
[SalesProducts]
SaleProductId Int, SaleId Int (FK_SaleMaster_SaleId) , Quantity Int
    
    
    SaleProductId    SalesId   Quantity
    
       1                10       2
       2                11       2
       3                12       1
       4                13       1
       5                14       3
       6                15       2
       7                16       5
       8                17       1
       9                18       1
      10                19       1

**[OUTPUT]**

The result set should have the sales of the login user + the users created by him and the further users created by the their

sub users where SalesMaster.StatusId=@StatusId

The following TotalSalesAmount is:

[SalesMaster].Price * [SalesProducts].Quantity
For eg: Total Sales amount for UserId: 1 , where SalesMaster.StatusId=1 is :
100*2+200*2+ 300*1 + 500*1 + 200*2 + 222*1 + 50*1+20*1= 2092

Similarly for UserId: 2 is where SalesMaster.StatusId=1
200*2+300*1 + 200*2 +500*1 + 200*2+20*1= 2020

case 1:
Lets Take SalesMaster.StatusId= 1, We need to show following:

UserId     TotalSalesAmount  OwnSaleAmount       AddedBy
 1          2092               250                 0
 2          2020               420                 1
 3          800                300                 2
 4          500                500                 3
 5            0                  0                 4
 6          200                200                 2
 7            0                  0                 3
 8          222                222                 1

Case 2:
In case (Vikas)UserId=3 will login where SalesMaster.StatusId=@StatusId,
Lets Take SalesMaster.StatusId= 2, We need to show following:

UserId   TotalSalesAmount    OwnSaleAmount    AddedBy
   3        100*3+111*5=855       0           2
   4         300                  0           3
   5        100*3=300           300           4
   7        111*5=555           555           3

I have this query running but giving wrong "TotalSaleAmount" and "OwnSaleAmount" results.

 with tblOwnSaleAmount as 
 ( 
      select UM.UserId, UM.AddedBy, sum( SM.Price*SP.Quantity ) as OwnSaleAmount 
      from UserMaster UM
      left join SalesMaster SM on SM.UserId = UM.UserId  and SM.statusID = 1 
      left join SalesProducts SP on SP.SaleId = SM.SalesId  
      where UM.UserId = 2 
      group by  UM.UserId ,UM.AddedBy                  

      union all 

      select UM.UserId, UM.AddedBy, 
      case when SM.statusID = 1 then ( SM.Price*SP.Quantity ) 
      else 0 end as OwnSaleAmount   
      from UserMaster UM
      join tblOwnSaleAmount on tblOwnSaleAmount.UserId = UM.AddedBy
      join SalesMaster SM on SM.UserId = UM.UserId 
      join SalesProducts SP on SP.SaleId = SM.SalesId 

 ),
  tbldistinctOwnSaleAmount as
 (
 select distinct UserId, AddedBy, OwnSaleAmount 
 from tblOwnSaleAmount
 ) ,
 tblTotalOwnSaleAmount as
 (
  select OSA.UserId,OSA.AddedBy, sum( OSA.OwnSaleAmount ) as OwnSaleAmount
  from tbldistinctOwnSaleAmount OSA
  group by OSA.UserId ,OSA.AddedBy
 )
 select OSA.* 
, TotalSalesAmount = isnull(
( 
    select sum( OSA1.OwnSaleAmount )
    from tblTotalOwnSaleAmount OSA1
    where OSA1.UserId > OSA.UserId                                                                   
), 0 )
+ OSA.OwnSaleAmount
 from tblTotalOwnSaleAmount OSA
 order by OSA.UserId

The problem is in last statememt of calculating the TotalSalesAmount,

It sums up all the Users ownSaleAmount where OSA1.UserId >= OSA.UserId. As in between some users dont have their child


please help