Oracle SQL Loop through Date Range

Question

I'm trying to write a query that does the following:

• Take the Start_Date and End_Date from the Visit table
• For each date inclusive in the date range, search the encounter table to see if a patient was seen by certain providers on that date
• Generate a table that shows each date and who saw the patient on that date.
• Notes:
• The patient may not have been seen on that date which should result to NULL or something similar
• More than one provider may have seen the patient on a date which should result in more than one line for that date.

Simplified Example:
Start_Date: 01-JAN-15
End_Date: 04-JAN-15

Desired Output:

╔══════╦══════════╦═══════╦═══════╗
║  ID  ║   DATE   ║ NAME  ║ TYPE  ║
╠══════╬══════════╬═══════╬═══════╣
║ 2222 ║ 1-Jan-15 ║ Smith ║ Note  ║
║ 2222 ║ 2-Jan-15 ║ Jones ║ Note  ║
║ 2222 ║ 2-Jan-15 ║ Smith ║ Order ║
║ 2222 ║ 3-Jan-15 ║ NULL  ║       ║
║ 2222 ║ 4-Jan-15 ║ Jones ║ Note  ║
╚══════╩══════════╩═══════╩═══════╝

Here is what I've got so far. This generates a list of when these certain providers saw the patients but it does not result NULL for those dates when they were not seen.

SELECT V.VISIT_ID, ET.ENCOUNTER_TRANSACTION_DATE, P.NAME_LAST, ETT.ENC_TRANS_TYPE_NAME
FROM VISIT V
RIGHT OUTER JOIN ENCOUNTER_TRANSACTION ET ON V.VISIT_ID = ET.VISIT_ID AND V.INSTITUTION_ID = ET.INSTITUTION_ID
INNER JOIN ENCOUNTER_TRANSACTION_TYPE ETT ON ET.ENCOUNTER_TYPE_ID = ETT.ENCOUNTER_TRANSACTION_TYPE_ID
INNER JOIN LOCAL_PROVIDER LP ON ET.ORDERING_PROVIDER_ID = LP.LOCAL_PROVIDER_ID
INNER JOIN PERSON_IDENTIFIER I ON I.IDENTIFIER = LP.PROVIDER_NUMBER AND I.IDENTIFIER_SYS_ID = LP.PROVIDER_NUMBER_SYS_ID  
INNER JOIN PERSON P ON P.PERSON_ID = I.PERSON_ID
WHERE
  V.INSTITUTION_ID = 1 AND
  V.END_DATE IS NOT NULL AND
  V.VOIDED_YN = 'N' AND
  V.CARE_SETTING_CODE = 'I' AND
  V.PATIENT_TEAM_ID IN (16, 17, 18) AND
  V.START_DATE >= (TRUNC(ADD_MONTHS(CURRENT_DATE, -1),'mon')) AND
  V.START_DATE <= (LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1))) AND
  I.IDENTIFIER IN (
    '1234', --Smith
    '4321', --Jones
  )
ORDER BY V.VISIT_ID ASC, ET.ENCOUNTER_TRANSACTION_DATE ASC;

Current Output (Missing the NULL line for 03-JAN-15):

╔══════╦══════════╦═══════╦═══════╗
║  ID  ║   DATE   ║ NAME  ║ TYPE  ║
╠══════╬══════════╬═══════╬═══════╣
║ 2222 ║ 1-Jan-15 ║ Smith ║ Note  ║
║ 2222 ║ 2-Jan-15 ║ Jones ║ Note  ║
║ 2222 ║ 2-Jan-15 ║ Smith ║ Order ║
║ 2222 ║ 4-Jan-15 ║ Jones ║ Note  ║
╚══════╩══════════╩═══════╩═══════╝

Answer 1

Here is how you might get a range of dates:

 SELECT DATE'2015-01-01' + LEVEL - 1
   FROM dual
CONNECT BY DATE'2015-01-01' + LEVEL - 1 < DATE'2015-02-01';

The above will get all dates in the range of January 1, 2015 to January 31, 2015.

What you could do, using the above, is plug in your start and end dates and create a CTE, then use an outer join on the dates:

WITH dr AS (
    SELECT DATE'2015-01-01' + LEVEL - 1 AS transaction_date
      FROM dual
   CONNECT BY DATE'2015-01-01' + LEVEL - 1 < DATE'2015-01-04'
)
SELECT V.VISIT_ID, dr.transaction_date
     , P.NAME_LAST, ETT.ENC_TRANS_TYPE_NAME
...
       ENCOUNTER_TRANSACTION ET RIGHT JOIN dr
    ON ET.ENCOUNTER_TRANSACTION_DATE = dr.transaction_date

UPDATE I had some time and I think I see how the above can be integrated into your query. I don't really have sample data for a SQL Fiddle (you have a lot of tables for that anyway). Here is where you might start, this should get all the appropriate visits plus every date along the range of dates on the visit (assuming no visit exceeds 30 days - adjust that accordingly):

WITH dr AS (
    SELECT LEVEL AS dd FROM dual
   CONNECT BY LEVEL <= 30 -- I'm assuming a max date range of 30; increase as you see fit
)
SELECT v.visit_id, v.start_date - 1 + dr.dd AS encounter_transaction_date
     , p.name_last, ett.enc_trans_type_name
  FROM visit v CROSS JOIN dr
 WHERE v.start_date - 1 + dr.dd < TRUNC(v.end_date) + 1
   AND v.institution_id = 1
   AND v.end_date IS NOT NULL
   AND v.voided_yn = 'N'
   AND v.care_setting_code = 'I'
   AND v.patient_team_id IN (16,17,18)
   AND v.start_date >= TRUNC(ADD_MONTHS(CURRENT_DATE, -1), 'MONTH')
   AND v.end_date < TRUNC(CURRENT_DATE, 'MONTH');

Then I think your outer joins should be LEFT JOINs from there (at least, if I understand correctly which I may not:

WITH dr AS (
    SELECT LEVEL AS dd FROM dual
   CONNECT BY LEVEL <= 30 -- I'm assuming a max date range of 30; increase as you see fit
)
SELECT v.visit_id, v.start_date - 1 + dr.dd AS encounter_transaction_date
  FROM visit v CROSS JOIN dr
  LEFT JOIN encounter_transaction et
    ON v.visit_id = et.visit_id
   AND v.institution_id = et.institution_id
   AND TRUNC(v.start_date - 1 + dr.dd) = et.encounter_transaction_date
  LEFT JOIN encounter_transaction_type ETT
    ON et.encounter_type_id = ett.encounter_transaction_type_id
  LEFT JOIN local_provider lp
    ON et.ordering_provider_id = lp.local_provider_id
  LEFT JOIN person_identifier i
    ON i.identifier = lp.provider_number
   AND i.identifier_sys_id = lp.provider_number_sys_id
   AND i.identifier IN (
       '1234', --Smith
       '4321' --Jones ** you had an extra comma here!
)
  LEFT JOIN person p
    ON p.person_id = i.person_id
 WHERE v.start_date - 1 + dr.dd < TRUNC(v.end_date) + 1
   AND v.institution_id = 1
   AND v.end_date IS NOT NULL
   AND v.voided_yn = 'N'
   AND v.care_setting_code = 'I'
   AND v.patient_team_id IN (16,17,18)
   AND v.start_date >= TRUNC(ADD_MONTHS(CURRENT_DATE, -1), 'MONTH')
   AND v.start_date < TRUNC(CURRENT_DATE, 'MONTH');