Need SQL query for team sales based on manager and running total as below logic

Question

1.00/5 (1 vote)

See more:

HI,

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

Thanks,
Yaswanth

What I have tried:

Tried in different ways in sql query but need

Posted 7-May-17 17:22pm

yaswanthkrishna

Updated 7-May-17 20:25pm

Maciej Los

v2

Add a Solution

Comments

barneyman 7-May-17 23:58pm

what have you tried?

(and TeamSales is a completely redundant column, it can be calc'd)

CHill60 8-May-17 4:56am

Just for the record that should be YTD (Year to Date) not MTD (Month to Date)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kosmas kafataridis · Accepted Answer · 2017-05-07T20:24:00

i believe that this your answer is

SQL

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