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