Counting only the MaxDate record in Oracle (Toad)

Question

With appropriate apologies to everyone as this is my first post, I'm just back into SQL queries after having been away for two and half years. And even then I wasn't the best. I want a Distinct Count of Persons (SSN) by Service (SHORT_NAM) but I only want to count their last record (with the Max(EndDate) irrespective of the the Short_Nam).

The problem is that a person can change services in the middle of year. The following code gives me everyone with their Service and EndDate. I can't figure out how to get a Distinct Count of the SSN's when using Max(EndDate) and grouping by Short_Nam. I only want to count the most recent record for them.

SELECT PLN.ENDDATE,
   PLN.SSN,
   SERV.SHORT_NAM    

FROM DDIS.CST_CST_PLN PLN
    INNER JOIN DDIS.CST_SERV SERV ON (SERV.PKSERVICE = PLN.FKSERVICE)
 WHERE TRIM(SERV.FISCALYR) = '2020' 
    AND STATUS IS NULL
    AND AUTHORIZED = 1
    AND TRIM(SERV.GROUPCODE) IN ('RES','SEM')
    AND NOT (SERV.SERVICE LIKE '%OUT%' OR
        SERV.SERVICE LIKE'%NEEDS%' OR
        SERV.SERVICE LIKE'%SNA%')

I've tried various things but seem to be running in circles. So If I have the following records:

SSN         EndDate   Short_Nam
111-11-1111  01/19/2020 FAM3
111-11-1111  06/30/2020 FAM3
222-22-2222  12/31/2019 FAM3
222-22-2222  04/20/2020 FAM3
222-22-2222  06/30/2020 SIL
333-33-3333  06/30/2020 FAM3

I want the results being:

USERS  Service
  2     FAM3
  1     SIL

Right now with everything I've tried using MaxDates and such, SSN 222-22-2222 will get counted under both FAM3 and SIL.

Where am I going wrong? I've tried setting up a temporary table in an initial WHERE clause. I've tried an Analytic Function something like 'max(my_date) over (partition by userid) max_my_date' but that also failed.

Any help? Thanks for the guidance. I'll get back up to speed slowly.

Hint to all of you. Don't take 2 1/2 years off! :)

score 1 · Answer 1 · answered Nov 27 '19 at 16:17

1

Your results seem consistent with:

select service, count(distinct ssn)
from t
group by service;

I am not sure that you actually care about the "last" user. You seem to just want to count each distinct user.

answered Nov 27 '19 at 16:17

Gordon Linoff

1,242,037
58
646
786

Gordon, thanks for the input but I don't think you grasped what I was asking for. Yes, I can get what you are talking about. But I don't want that. I only want to count the user in the **last** service the user was in. For example, in the list above I only want to count 222-22-2222 in SIL and not count them in FAM3. Hope that helps. – RodeDawg Dec 02 '19 at 13:13

Counting only the MaxDate record in Oracle (Toad)

1 Answers1