sqlsql-serverazure-sql-databasesql-server-2016

Calculate Sales By Sales Person


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?


Solution

  • 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 |
    +-----------+-------------+---------------------+---------------+-------------------------------+