I have a table with below format.
Sample table and data
declare @sales table
(productid int, TransDate date,SalesPerson varchar(100),Amount bigint)
insert into @sales
values
(1 , 20150101, 50 , 'S1'),
(1 , 20150201, 50 , 'S1'),
(1 , 20150301, 50 , 'S1'),
(1 , 20150401, 50 , 'S1'),
(1 , 20150501, 50 , 'S1'),
(1 , 20150601, 50 , 'S2'),
(1 , 20150701, 50 , 'S2'),
Now, I need to calculate the total sales of each rep and the related sales(sales performed by all sales persons other than than the main sales person)
For example, For Sales Person S1, his sales are 250 and related agents(S2) sales(who also involved in the sales serviced by S1 for products 1) will be 100.
The output should be:
SalesPerson Date MySalesAmount RelatedSalesPersonSalesAmount
S1 20150101 50 0
S1 20150201 50 0
S1 20150301 50 0
S1 20150401 50 0
S1 20150501 50 0
S1 20150601 0 50
S1 20150701 0 50
S2 20150101 0 50
S2 20150201 0 50
S2 20150301 0 50
S2 20150401 0 50
S2 20150501 0 50
S2 20150601 50 0
S2 20150701 50 0
Can anyone help on this to achieve above output with a query?
Check the following query:
declare @sales table
(productid int, TransDate date,SalesPerson varchar(100),Amount bigint);
insert into @sales(productid, TransDate, Amount, SalesPerson)
values
(1, '20150101', 50, 'S1'),
(1, '20150201', 50, 'S1'),
(1, '20150301', 50, 'S1'),
(1, '20150401', 50, 'S1'),
(1, '20150501', 50, 'S1'),
(1, '20150601', 50, 'S2'),
(1, '20150701', 50, 'S2'),
(1, '20150601', 10, 'S3'),
(1, '20150701', 10, 'S3');
select distinct
SalesPerson,
sum(Amount) over (partition by productid, SalesPerson) as MySalesAmount,
sum(Amount) over (partition by productid) -
sum(Amount) over (partition by productid, SalesPerson) as RelatedSalesPersonSalesAmount
from @sales;
Output:
+-------------+---------------+-------------------------------+
| SalesPerson | MySalesAmount | RelatedSalesPersonSalesAmount |
+-------------+---------------+-------------------------------+
| S1 | 250 | 120 |
| S2 | 100 | 270 |
| S3 | 20 | 350 |
+-------------+---------------+-------------------------------+
Test it online with rextester.
Update:
select
x.productid,
x.SalesPerson,
y.TransDate as [Date],
iif(x.SalesPerson = y.SalesPerson, y.Amount, 0) as MySalesAmount,
iif(x.SalesPerson = y.SalesPerson, 0, y.Amount) as RelatedSalesPersonSalesAmount
from (select distinct productid, SalesPerson from @sales) as x
join @sales as y
on x.productid = y.productid
order by x.productid, x.SalesPerson;
Output:
+-----------+-------------+---------------------+---------------+-------------------------------+
| productid | SalesPerson | Date | MySalesAmount | RelatedSalesPersonSalesAmount |
+-----------+-------------+---------------------+---------------+-------------------------------+
| 1 | S1 | 01.01.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.02.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.03.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.04.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.05.2015 00:00:00 | 50 | 0 |
| 1 | S1 | 01.06.2015 00:00:00 | 0 | 50 |
| 1 | S1 | 01.07.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.01.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.02.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.03.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.04.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.05.2015 00:00:00 | 0 | 50 |
| 1 | S2 | 01.06.2015 00:00:00 | 50 | 0 |
| 1 | S2 | 01.07.2015 00:00:00 | 50 | 0 |
+-----------+-------------+---------------------+---------------+-------------------------------+