2

I'm sorry if this happens to be a re-post however looking through all of the previous questions I could find with similar wording I have not been able to find a working answer.

I have a trainingHistory table that has a record for every new training. The training can be done by multiple trainers. Clients can have multiple trainers.

What I am trying to accomplish is to COUNT the number of clients that was last trained by each trainer.

Example:

clientID | trainDate          | trainerID
101      | 2012-03-13 10:58:11| 10
101      | 2012-03-12 10:58:11| 11
102      | 2012-03-15 10:58:11| 10
102      | 2012-03-09 10:58:11| 12
103      | 2012-03-08 10:58:11| 7

So the end result I am looking for would be:

Results
trainerID | count
10        | 2
7         | 1

I've tried quite a few different queries and looked over quite a few answers, including this one here Using sub-queries in SQL to find max(count()) but have so far been unable to get the desired result.

What I keep getting is like this:

Results
trainerID | count
10        | 5
7         | 5

How can I get an accurate count per trainer as opposed to an overall total?

The closest I've gotten is this:

SELECT      t.trainerName,
            t.trainerID,
            (
                SELECT      COUNT(lastTrainerCount)
                FROM        (
                                SELECT      MAX(th.clientID) AS lastTrainerCount
                                FROM        trainingHistory th
                                GROUP BY    th.clientID
                            )   AS lastTrainerCount
            )
FROM        trainers t
INNER JOIN  trainingHistory th ON (th.trainerID = t.trainerID)
WHERE       th.trainingDate BETWEEN '12/14/14' AND '02/07/15'
GROUP BY    t.trainerName, t.trainerID

Which results in:

Results
trainerID | count
10        | 1072
7         | 1072

Using SQL Server 2012

Appreciate any help you can provide.

Community
  • 1
  • 1
Caleb Palmquist
  • 448
  • 2
  • 7
  • 15

1 Answers1

1

First find the max trainDate per clientID in sub-select. Then count the trainerID in outer query. Try this.

select trainerID,count(trainerID) [Count]
From
(
select clientID,trainDate,trainerID,
       row_number()over(partition by clientID order by trainDate Desc) Rn
From yourtable
) A
where Rn=1
Group by trainerID

SQLFIDDLE DEMO

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172