问题描述
HI，
我有4列，如下所示我的过滤器是EmpName和月份
1.EmpName
2.管理员
3.EmpSales
4.Month
我需要团队销售和MTD来自查询的销售栏根据以下逻辑给出结果
团队销售逻辑：如果我选择了Emp A，如果他也是经理，那么团队销售为3450（这是第1个月A + B + C的总和，因为我们必须总和A销售额
MTD销售逻辑：如果我选择Emp A并且他也是经理然后MTD团队第1个月的销售额为3450，如果我选择第2个月，它必须显示5660（这是第1个月和第2个月的总和），如果他是经理，我们必须将A销售额加起来。
I have 4 columns like below here my filters are EmpName and Month
1.EmpName
2.Manager
3.EmpSales
4.Month
I need Team Sales and MTD sales column from query which gives result based on below logic
Team Sales Logic :If I select Emp A and if he is also manager then Team Sales as 3450 (which is sum of A+B+C) for month 1 because we have to sum A sales also
MTD Sales Logic : If I select Emp A and if he is also manager then then MTD Team sales value 3450 for month 1 and if I select month 2 it must show 5660(which is sum of month 1 and month 2) where we have to sum A sales also if he is manager.
Month Emp sales Manager TeamSales MTD Team Sales
1 A 100 D 3450 3450
1 A 150 D 3450 3450
1 A 200 D 3450 3450
1 A 250 D 3450 3450
1 A 300 D 3450 3450
1 A 350 D 3450 3450
1 A 200 D 3450 3450
3 B 350 A 0 0
1 B 400 A 0 0
1 B 450 A 0 0
2 C 100 A 0 0
2 C 200 A 0 0
2 C 250 A 0 0
2 C 300 A 0 0
2 C 400 A 0 0
3 B 500 A 0 0
3 B 500 A 0 0
3 B 450 A 0 0
3 B 450 A 0 0
3 B 450 A 0 0
3 A 500 D 4550 10150
3 A 500 D 4550 10150
3 A 250 D 4550 10150
1 C 200 A 0 0
1 C 200 A 0 0
1 C 150 A 0 0
3 C 150 A 0 0
3 C 150 A 0 0
3 C 150 A 0 0
1 B 250 A 0 0
1 B 250 A 0 0
3 C 150 A 0 0
2 B 150 A 0 0
2 B 250 A 0 0
2 A 250 D 2150 5600
2 A 250 D 2150 5600
我尝试了什么：
我尝试了不同的方法来获得经理逻辑。
What I have tried:
I tried in different ways to get the manager logic.
推荐答案
DECLARE
@EmpName VARCHAR(50),
@Month INT
-- Your filters
SET @EmpName = 'A'
SET @Month = 3
-- Value for filters
SELECT @EmpName Emp, @Month Month,
(SELECT SUM(Sales) FROM dbo.[Table] WHERE (Employee = @EmpName OR Manager = @EmpName) AND Month = @Month) TeamSales,
(SELECT SUM(Sales) FROM dbo.[Table] WHERE (Employee = @EmpName OR Manager = @EmpName) AND Month <= @Month) 'MTD Team Sales'
表结构：
名称：表
Table structure:
Name: table
Month int Checked
Employee varchar(50) Checked
Sales int Checked
Manager varchar(50) Checked
表中的值：
Values in Table:
Month Employee Sales Manager
1 A 100 D
1 A 150 D
1 A 200 D
1 A 250 D
1 A 300 D
1 A 350 D
1 A 200 D
3 B 350 A
1 B 400 A
1 B 450 A
2 C 100 A
2 C 200 A
2 C 250 A
2 C 300 A
2 C 400 A
3 B 500 A
3 B 500 A
3 B 450 A
3 B 450 A
3 B 450 A
3 A 500 D
3 A 500 D
3 A 250 D
1 C 200 A
1 C 200 A
1 C 150 A
3 C 150 A
3 C 150 A
3 C 150 A
1 B 250 A
1 B 250 A
3 C 150 A
2 B 150 A
2 B 250 A
2 A 250 D
2 A 250 D
这篇关于如果团队成员也是经理，则对团队销售进行Sql查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！