问题描述
HI，
我有4列，如下所示我的过滤器是EmpName和月份
1.EmpName
2.管理员
3.EmpSales
4.Month
我需要团队销售和MTD来自查询的销售栏根据以下逻辑给出结果
团队销售逻辑：如果我选择Emp A，我们的销售额为100，团队销售额为450（这是总和） A + E + F）第1个月
MTD销售逻辑：如果我选择Emp A月1然后它必须显示与团队销售值450相同但是如果我选择第2个月必须显示1050（这是第1个月和第2个月的总和）
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 we have sales as 100 and Team Sales as 450 (which is sum of A+E+F) for month 1
MTD Sales Logic : If I select Emp A month 1 then it must show same as team sales value 450 but if I select month 2 it must show 1050(which is sum of month 1 and month 2)
EmpName Manager Empsales TeamSales Month MTDsales
A X 100 450 1 450
E X 200 450 1 450
F X 150 450 1 450
B Y 100 300 1 300
C Y 200 300 1 300
D Z 150 250 1 250
G Z 100 250 1 250
A X 150 600 2 1050
E X 250 600 2 1050
F X 200 600 2 1050
B Y 100 350 2 650
C Y 250 350 2 650
D Z 200 300 2 550
G Z 100 300 2 550
谢谢，
Yaswanth
我尝试过：
在sql查询中以不同的方式尝试但需要
Thanks,
Yaswanth
What I have tried:
Tried in different ways in sql query but need
推荐答案
CREATE TABLE #test (EmpName NVARCHAR(50), Manager NVARCHAR(50), Empsales INT, [Month] int);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('A', 'X', 100,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('E' , 'X' , 200 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('F' , 'X' , 150 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('B' , 'Y' , 100 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('C' , 'Y' , 200 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('D' , 'Z' , 150 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('G' , 'Z' , 100 ,1);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('A' , 'X' , 150 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('E' , 'X' ,250 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('F' ,'X', 200 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('B', 'Y' , 100 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('C' , 'Y' , 250,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('D' , 'Z' , 200 ,2);
insert into #test (EmpName, Manager , Empsales , [Month]) values ('G' , 'Z' , 100 ,2);
declare @ToMonth int=2;
declare @FromMonth int=1;
SELECT a.EmpName, a.Manager , a.[Month], SUM(Empsales) Empsales, TeamSales, MTDsales FROM #test a JOIN (SELECT Manager, [Month], SUM(Empsales) TeamSales FROM #test where [Month]=@tomonth group by Manager, [Month]) TeamSales on a.Manager=TeamSales.Manager and a.[Month]=TeamSales.[Month]
JOIN (SELECT Manager, Max([Month]) [Month], SUM(Empsales) MTDsales FROM #test where [Month]>=@FromMonth and [Month]<=@ToMonth group by Manager) MTDsales on a.Manager=MTDsales.Manager and a.[Month]=MTDsales.[Month]
group by a.EmpName, a.Manager , a.[Month], TeamSales, MTDsales
DROP TABLE #test
输出
output
EmpName Manager Month Empsales TeamSales MTDsales
A X 2 150 600 1050
B Y 2 100 350 650
C Y 2 250 350 650
D Z 2 200 300 550
E X 2 250 600 1050
F X 2 200 600 1050
G Z 2 100 300 550
这篇关于需要基于经理和运行总计的团队销售的SQL查询，如下面的逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！