Cheaplogic Cheaplogic - 7 months ago 10
SQL Question

SQL NOOB - Oracle joins and Row Number

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.

vkp vkp
Answer

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');
Comments