SQL NOOB - Oracle joins and Row Number

Question

I was hoping to get some guidance on a SQL script I am trying to put together for Oracle database 11g.

I am attempting to perform a count of claims from the 'claim' table, and order them by year / month / and enterprise.

I was able to get a count of claims and order them like I would like, however I need to pull data from another table and I am having trouble combining the 'row_number' function with a join.

Here is my script so far:

SELECT TO_CHAR (SYSTEM_ENTRY_DATE, 'YYYY') YEAR,
     TO_CHAR (SYSTEM_ENTRY_DATE, 'MM') MONTH,
     ENTERPRISE_IID,
     COUNT (*) CLAIMS
FROM (SELECT CLAIM.CLAIM_EID,
    CLAIM.SYSTEM_ENTRY_DATE,
    CLAIM.ENTERPRISE_IID,
    ROW_NUMBER () OVER (PARTITION BY CLAIM.CLAIM_EID, CLAIM.ENTERPRISE_IID
    ORDER BY CLAIM.SYSTEM_ENTRY_DATE DESC) RN
        FROM CLAIM
        WHERE CLAIM_IID IN (SELECT DISTINCT (CLAIM_IID)
            FROM CLAIM_LINE
            WHERE STATUS <> 'D')
            AND CLAIM.CONTEXT = '1'
            AND CLAIM.CLAIM_STATUS = 'A'
            AND CLAIM.LAST_ANALYSIS_DATE IS NOT NULL)
WHERE RN = 1
GROUP ENTERPRISE_IID,
TO_CHAR (SYSTEM_ENTRY_DATE, 'YYYY'),
TO_CHAR (SYSTEM_ENTRY_DATE, 'MM');

So far all of my data is coming from the 'claim' table. This pulls the following result:

YEAR MONTH ENTERPRISE_IID     CLAIMS
---- ----- -------------- ----------
2016 01                 6          1
2015 08                 6          3
2016 02                 6          2
2015 09                 6          2
2015 07                 6          2
2015 09                 5         22
2015 11                 5         29
2015 12                 5         27
2016 04                 5          8
2015 07                 5         29
2015 05                 5         15
2015 06                 5          5
2015 10                 5         45
2016 03                 5         54
2015 03                 5         10
2016 02                 5         70
2016 01                 5         55
2015 08                 5         32
2015 04                 5         12

19 rows selected.

The enterprise_IID is the primary key on the 'enterprise' table. The 'enterprise' table also contains the 'name' attribute for each entry. I would like to join the claim and enterprise table in order to show the enterprise name for this count, and not the enterprise_IID.

As you can tell I am rather new to Oracle and SQL, and I am a bit stuck on this one. I was thinking that I should do an inner join between the two tables, but I am not quite sure how to do that when using the row_number function.

Or perhaps I am taking the wrong approach here, and someone could push me in another direction.

Here is what I tried:

SELECT TO_CHAR (SYSTEM_ENTRY_DATE, 'YYYY') YEAR,
    TO_CHAR (SYSTEM_ENTRY_DATE, 'MM') MONTH,
    ENTERPRISE_IID,
    ENTERPRISE.NAME,
    COUNT (*) CLAIMS
FROM (SELECT CLAIM.CLAIM_EID,
    CLAIM.SYSTEM_ENTRY_DATE,
    CLAIM.ENTERPRISE_IID,
    ROW_NUMBER () OVER (PARTITION BY CLAIM.CLAIM_EID, CLAIM.ENTERPRISE_IID
    ORDER BY CLAIM.SYSTEM_ENTRY_DATE DESC) RN
        FROM CLAIM, enterprise
        INNER JOIN ENTERPRISE
        ON CLAIM.ENTERPRISE_IID = ENTERPRISE.ENTERPRISE_IID
        WHERE CLAIM_IID IN (SELECT DISTINCT (CLAIM_IID)
            FROM CLAIM_LINE
            WHERE STATUS <> 'D')
            AND CLAIM.CONTEXT = '1'
            AND CLAIM.CLAIM_STATUS = 'A'
            AND CLAIM.LAST_ANALYSIS_DATE IS NOT NULL)
WHERE RN = 1
GROUP BY ENTERPRISE.NAME,
ENTERPRISE_IID,
TO_CHAR (SYSTEM_ENTRY_DATE, 'YYYY'),
TO_CHAR (SYSTEM_ENTRY_DATE, 'MM');

Thank you in advance!

"Desired Output"

YEAR MONTH NAME     CLAIMS
---- ----- ---- ----------
2016 01    Ent1        1
2015 08    Ent1        3
2016 02    Ent1        2
2015 09    Ent1        2
2015 07    Ent1        2
2015 09    Ent2       22
2015 11    Ent2       29
2015 12    Ent2       27
2016 04    Ent2        8
2015 07    Ent2       29
2015 05    Ent2       15
2015 06    Ent2        5
2015 10    Ent2       45
2016 03    Ent2       54
2015 03    Ent2       10
2016 02    Ent2       70
2016 01    Ent2       55
2015 08    Ent2       32
2015 04    Ent2       12

19 rows selected.

Answer 1

You can try this. Joins can be used when calculating row numbers with row_number function.

SELECT TO_CHAR (SYSTEM_ENTRY_DATE, 'YYYY') YEAR,
    TO_CHAR (SYSTEM_ENTRY_DATE, 'MM') MONTH,
    ENTERPRISE_IID,
    NAME,
    COUNT (*) CLAIMS
FROM (SELECT CLAIM.CLAIM_EID,
      CLAIM.SYSTEM_ENTRY_DATE,
      CLAIM.ENTERPRISE_IID,
      ENTERPRISE.NAME,
      ROW_NUMBER () OVER (PARTITION BY CLAIM.CLAIM_EID, CLAIM.ENTERPRISE_IID
      ORDER BY CLAIM.SYSTEM_ENTRY_DATE DESC) RN
      FROM CLAIM --, enterprise (this is not required as the table is being joined already)
      INNER JOIN ENTERPRISE ON CLAIM.ENTERPRISE_IID = ENTERPRISE.ENTERPRISE_IID
      INNER JOIN (SELECT DISTINCT CLAIM_IID FROM CLAIM_LINE WHERE STATUS <> 'D') CLAIM_LINE 
      ON CLAIM.CLAIM_IID = CLAIM_LINE.CLAIM_IID
      WHERE CLAIM.CONTEXT = '1'
      AND CLAIM.CLAIM_STATUS = 'A'
      AND CLAIM.LAST_ANALYSIS_DATE IS NOT NULL) t
WHERE RN = 1
GROUP BY NAME, --ENTERPRISE.NAME (The alias ENTERPRISE is not accessible here.)
ENTERPRISE_IID,
TO_CHAR(SYSTEM_ENTRY_DATE, 'YYYY'),
TO_CHAR(SYSTEM_ENTRY_DATE, 'MM');

Answer 2

I'd write the query like this:

SELECT TO_CHAR(TRUNC(c.system_entry_date,'MM'),'YYYY') AS year
     , TO_CHAR(TRUNC(c.system_entry_date,'MM'),'MM')   AS month
     , e.enterprise_name                               AS name 
     , COUNT(*)                                        AS claims
  FROM (
         SELECT r.claim_eid
              , r.enterprise_iid
              , MAX(r.system_entry_date) AS system_entry_date
           FROM ( SELECT DISTINCT l.claim_iid
                    FROM claim_line l
                   WHERE l.status <> 'D'
                ) d
           JOIN claim r
             ON r.claim_iid = d.claim_iid
            AND r.context = '1'
            AND r.claim_status = 'A'
            AND r.last_analysis_date IS NOT NULL
          GROUP
             BY r.claim_eid
              , r.enterprise_iid
       ) c
  JOIN enterprise e
    ON e.enterprise_iid = c.enterprise_iid
 GROUP
    BY c.enterprise_iid
     , TRUNC(c.system_entry_date,'MM')
     , e.enterprise_name
 ORDER
    BY e.enterprise_name
     , TRUNC(c.system_entry_date,'MM')

A few notes:

I prefer to qualify ALL column references with the table name or short table alias, and assign aliases to all inline views.

Since the usage of ROW_NUMBER() appears to be get the "latest" system_entry_date for a claim and eliminate duplicates, I'd prefer to use a GROUP BY and a MAX() aggregate.

I prefer to use a join operation rather than the NOT IN (subquery) pattern. (Or, I would tend to use a NOT EXISTS (correlated subquery) pattern.

I don't think it matters too much if you use TO_CHAR or EXTRACT. The TO_CHAR gets you the leading zero in the month, I don't think EXTRACT(MONTH ) gets you the leading zero. I'd use whichever gets me closest to the resultset I need.Personally, I would return just a single column, either containing the year and month as one string e.g. TO_CHAR( , 'YYYYMM') or just a DATE value. It all depends what I'm going to be doing with that.