Sql query for team sales if team member is also manager.

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 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.

Posted 11-May-17 19:43pm

yaswanthkrishna

Updated 15-May-17 21:19pm

v2

Add a Solution

Comments

CHill60 12-May-17 11:38am

"I tried in different ways to get the manager logic. " - why don't you share those attempts with us?

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)

Zunayed Shahriar · Answer 1 · 2017-05-15T21:19:00

I think this is what are you looking for.
Done with MSSQL.

SQL

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