按销售人员计算销售额
按销售人员计算销售额
提问于 2019-03-15 03:05:54
我有一张以下格式的桌子。
样本表与数据
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'),现在，我需要计算每个销售代表的总销售额和相关的销售(除主要销售人员以外的所有销售人员执行的销售)。
例如，对于销售人员S1，他的销售额为250个，相关代理(S2)销售(也参与S1为产品1提供服务的销售人员)为100。
产出应是：
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有人能在这方面提供帮助，通过查询实现上述输出吗？
Stack Overflow用户
回答已采纳
发布于 2019-03-15 05:15:35
请检查以下查询：
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;输出：
+-------------+---------------+-------------------------------+
| SalesPerson | MySalesAmount | RelatedSalesPersonSalesAmount |
+-------------+---------------+-------------------------------+
| S1 | 250 | 120 |
| S2 | 100 | 270 |
| S3 | 20 | 350 |
+-------------+---------------+-------------------------------+用雷克斯试验器在线测试。
更新：
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;输出：
+-----------+-------------+---------------------+---------------+-------------------------------+
| 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 |
+-----------+-------------+---------------------+---------------+-------------------------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55174971
复制相关文章
相似问题
腾讯云开发者
