CODEWITHSUNDEEP
sqlsql-server
Nipius
Nipius

Reputation: 27

Historical sum for current group

I have a SQL database with people, from which I want to see how much experience each person has in a specific department.

In the current query I have the following code:

SELECT [PERSON_ID]
,sum(case when [DEPARTMENT] = 'Marketing' then 1 else 0 end) as Exp_Marketing
,sum(case when [FUNCTION_DESC] = 'Finance' then 1 else 0 end) as Exp_Finance
FROM [xxxx].[xxxx].[xxxx]
GROUP BY [PERSON_ID]

Each person has one row for the months of service, so a person with 12 months of experience in Finance has a value of 12 in the Exp_Finance column.

The issue however is that the result now shows the outcome for all people. Also the one who already left the organization. How can I make sure the result only shows the historical information for the people currently part in the organization. In other words, the ones actually having a row with "2018M06" as value for the Date column.

Upvotes: 2

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

You can use a having clause:

SELECT [PERSON_ID],
      sum(case when [DEPARTMENT] = 'Marketing' then 1 else 0 end) as Exp_Marketing,
      sum(case when [FUNCTION_DESC] = 'Finance' then 1 else 0 end) as Exp_Finance
FROM [xxxx].[xxxx].[xxxx]
GROUP BY [PERSON_ID]
HAVING MAX([DATE]) = '2018M06';

Your month format seems amenable to using MAX().

Upvotes: 1

Jason W
Jason W

Reputation: 13199

You should add an EXISTS within a WHERE clause so you only include people meeting your criteria.

SELECT [PERSON_ID]
,sum(case when [DEPARTMENT] = 'Marketing' then 1 else 0 end) as Exp_Marketing
,sum(case when [FUNCTION_DESC] = 'Finance' then 1 else 0 end) as Exp_Finance
FROM [xxxx].[xxxx].[xxxx] A
WHERE EXISTS (SELECT * FROM [xxxx].[xxxx].[xxxx] B
   WHERE A.PERSON_ID = B.PERSON_ID AND B.[DATE] = '2018M06')
GROUP BY [PERSON_ID]

Upvotes: 1

Related Questions