CODEWITHSUNDEEP
sqloracle-database
bawpie
bawpie

Reputation: 477

ORACLE SQL Returning most recently added record detail

Edit: Solved with a lot of help from Dems, I will post a truncated version of the query which is now working very well!

            SELECT
  *

FROM

    O_PERSONS

    /*The below left join returns the most recently added ethnicity classification*/
  LEFT JOIN 

  (SELECT
   ROW_NUMBER() OVER (PARTITION BY O_CLASSIFICATIONS.CLA_SUBJECT_ID ORDER BY O_CLASSIFICATIONS.CLA_DATE_NOTIFIED DESC) AS Sequence_ID,
   O_CLASSIFICATIONS.CLA_CAT_ID,
   O_CLASSIFICATIONS.CLA_SUBJECT_ID
   FROM
   O_CLASSIFICATIONS
   WHERE
   O_CLASSIFICATIONS.CLA_SUBJECT_IND = 'P'
   AND
   O_CLASSIFICATIONS.CLA_TOP_CAT_ID   =  'ETHNIC'
   AND
   O_CLASSIFICATIONS.CLA_CAT_ID <> 'DECLINED'
   ) ETHNIC 
   ON ETHNIC.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
   AND ETHNIC.Sequence_ID = 1

    /*The below left join returns the most recently added PCG classification*/
   LEFT JOIN 

  (SELECT
   ROW_NUMBER() OVER (PARTITION BY O_CLASSIFICATIONS.CLA_SUBJECT_ID ORDER BY O_CLASSIFICATIONS.CLA_DATE_NOTIFIED DESC) AS Sequence_ID,
   O_CLASSIFICATIONS.CLA_CAT_ID,
   O_CLASSIFICATIONS.CLA_SUBJECT_ID
   FROM
   O_CLASSIFICATIONS
   WHERE
   O_CLASSIFICATIONS.CLA_SUBJECT_IND = 'P'
   AND
   O_CLASSIFICATIONS.CLA_TOP_CAT_ID   =  'PRIMARY'
   AND
   O_CLASSIFICATIONS.CLA_CAT_ID <> 'DECLINED'
   ) PCG
   ON PCG.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
   AND PCG.Sequence_ID = 1


  WHERE

   O_PERSONS.PER_ID LIKE 'P%'

I am currently trying to write a query which will return certain details for clients on our system. Whilst a client will only have one date of birth, gender, and P ID, they may have multiple ethnicities (as a result of data quality) and also client groups (this is more legitimate as a client's needs may change). However, for these particular items, I am only interested in the most recently added ethnicities or client groups. Below I have written a query which assigns a row number to the ethnicity and to the client group with a plan to return only those which equal 1, as these generally will be the most recent. However, I have run into 2 issues, one of which I can resolve, the other of which I'm not so sure.

First off, some clients will not have an ethnicity or a client group recorded. When running the below query, they return with very odd row numbers (often in the thousands). However, I know in my 'where' that I could specify that a row is returned if client group or ethnicity was empty (as these will be data quality cases which need to be addressed).

The second issue is slightly trickier which I will try to represent with a table below. Please note I have abbreviated the table for ease of input and understanding.

  P ID Ethnicity   PCG RN E RN P
  P1   WB          OV  1    2 
  P1   WI          OV  2    1

The above client had two ethnicities, though WB is the most recent (therefore the 1 in Row Number E is correct). However, the client has only had one PCG recorded but the row number returns a 2 in the first row (arguably the row I want to return). I'm not sure why though I guess because ETHNIC.CLA_SUBJECT_ID is joined on O_PERSONS.PER_ID and PER_ID occurs twice, that is why it thinks there is two rows for that particular field. However, even if that is the case, is there a way to force just 1's to appear in one row? Or could I do this a completely different way? Hopefully this query makes some sense, apologies if parts are unclear. Thanks,

                         SELECT
          O_PERSONS.PER_ID as "P ID",
          olm_bo.get_per_name(O_PERSONS.PER_ID) as "Full Name",
          O_PERSONS.PER_BIRTH_DATE as "Date of Birth",
          case 
            when O_PERSONS.PER_DECEASED_DATE is null then FLOOR(MONTHS_BETWEEN(sysdate,O_PERSONS.PER_BIRTH_DATE)/12) 
            else FLOOR(MONTHS_BETWEEN(O_PERSONS.PER_DECEASED_DATE,O_PERSONS.PER_BIRTH_DATE)/12) 
          end as "Age",
          O_PERSONS.PER_DECEASED_DATE as "Date Deceased",
          olm_bo.get_gender_desc('P', O_PERSONS.PER_GENDER) as "Gender",
          CASE 
            WHEN ETHNIC.CLA_CAT_ID IN ('C1','C2','C3','C4','ABAN','AIND','AOTH','APKN') THEN 'Asian or Asian British'
            ELSE NULL
          End as "Ethnicity - Top" ,
          CASE 
            WHEN ETHNIC.CLA_CAT_ID IN ('BAFR','D2') THEN 'African'
            ELSE NULL
          End as "Ethnicity - Detail" ,
          CASE 
            WHEN PCG.CLA_CAT_ID IN ('ASYLUM','REFUGEE') THEN 'Asylum Seeker/Refugee'
            ELSE NULL
          End as "PCG",
          CASE 
            WHEN PCG.CLA_CAT_ID IN ('ASYLUM','REFUGEE') THEN 'Asylum Seeker/Refugee'
            ELSE NULL
          End as "PCG - Top",
          CASE 
            WHEN PCG.CLA_CAT_ID IN ('ASYLUM','REFUGEE') THEN 'Asylum Seeker/Refugee'
            ELSE NULL
          End as "PCG - DETAIL",

          to_char(row_number() over(PARTITION BY  ETHNIC.CLA_SUBJECT_ID ORDER BY abs(sysdate - ETHNIC.CLA_DATE_NOTIFIED) asc)) as "Row Number E",
          to_char(row_number() over(PARTITION BY  PCG.CLA_SUBJECT_ID ORDER BY abs(sysdate - PCG.CLA_DATE_NOTIFIED) asc))as "Row Number P"

        FROM

          O_PERSONS

          LEFT JOIN O_CLASSIFICATIONS ETHNIC ON ETHNIC.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
          AND ETHNIC.CLA_SUBJECT_IND = 'P'
          AND ETHNIC.CLA_TOP_CAT_ID   =  'ETHNIC'
          AND ETHNIC.CLA_CAT_ID <> 'DECLINED'

          LEFT JOIN O_CLASSIFICATIONS PCG ON PCG.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
          AND PCG.CLA_SUBJECT_IND = 'P'
          AND PCG.CLA_TOP_CAT_ID   =  'PRIMARY'
          AND PCG.CLA_CAT_ID <> 'DECLINED'

          WHERE
          /*Following line excludes any clients whose is less than 18)*/
            O_PERSONS.PER_BIRTH_DATE > trunc(add_months(O_PERSONS.PER_BIRTH_DATE,-216))
            AND  O_PERSONS.PER_ID LIKE 'P%'

Upvotes: 0

Views: 500

Answers (1)

MatBailie
MatBailie

Reputation: 86775

You should apply ROW_NUMBER() before you do the joins.

SELECT
  *     -- Your calculations here, '*' used for brevity
FROM
  O_PERSONS
LEFT JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY CLA_SUBJECT_ID ORDER BY CLA_DATE_NOTIFIED DESC) AS sequence_id,
    *
  FROM
    O_CLASSIFICATIONS
  WHERE
        CLA_SUBJECT_IND =  'P'
    AND CLA_TOP_CAT_ID  =  'ETHNIC'
    AND CLA_CAT_ID      <> 'DECLINED'
)
  ETHNIC
    ON  ETHNIC.CLA_SUBJECT_ID = O_PERSONS.PER_ID
    AND ETHNIC.sequence_id    = 1
LEFT JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY CLA_SUBJECT_ID ORDER BY CLA_DATE_NOTIFIED DESC) AS sequence_id,
    *
  FROM
    O_CLASSIFICATIONS
  WHERE
        CLA_SUBJECT_IND =  'P'
    AND CLA_TOP_CAT_ID  =  'PRIMARY'
    AND CLA_CAT_ID      <> 'DECLINED'
)
  PCG
    ON  PCG.CLA_SUBJECT_ID = O_PERSONS.PER_ID
    AND PCG.sequence_id    = 1
WHERE
     /*Following line excludes any clients whose is less than 18)*/
     O_PERSONS.PER_BIRTH_DATE > trunc(add_months(O_PERSONS.PER_BIRTH_DATE,-216))
AND  O_PERSONS.PER_ID LIKE 'P%'

Upvotes: 1

Related Questions