Oracle SQL循环通过日期范围 [英] Oracle SQL Loop through Date Range

问题描述

我正在尝试编写一个查询，执行以下操作：

• 从访问表中获取Start_Date和End_Date


• 对于日期范围内包含的每个日期，搜索遇到的表
  ，以查看该日期某些提供者是否看到患者


• 生成一张表格，显示每个日期，谁看到病人在
  日期。


• 注意事项：


• 该日期可能没有看到患者可能导致NULL或类似的情况


• 不止一个供应商可能已经看到患者在某个日期应该导致该日期多于一行。

简化示例：

Start_Date：01-JAN-15

End_Date：04-JAN-15

期望的输出：

 ╔═════════════════ ╦═══════════════╗
║ID║DATE║NAME║TYPE║
╠═══════════════ ══╬═════════════════════════════$║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║║ ║注║
║2222║2-Jan-15║史密斯║订单║
║2222║3-Jan-15║NULL║║
║2222║4-Jan-15║琼║注║
╚══════╩ ═════════════════════╝
 b b b b b b b b b b b b b b b b b b b b b $ b 
这是我到目前为止。这产生了一些这些特定提供者看到病人的列表，但是当没有看到这些病人时，这些日期不会导致NULL。
  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（和
 V.START_DATE< =（LAST_DAY（ADD_MONTHS（CURRENT_DATE，-1）））AND 
 I.IDENTIFIER IN（
'1234（CURRENT_DATE，-1），'mon' '， - 史密斯
'4321'， - 琼斯
）
 ORDER BY V.VISIT_ID ASC，ET.ENCOUNTER_TRANSACTION_DATE ASC; 
  
当前输出（缺少03-JAN-15的空行）：
 ╔═══════════════════════════════ ══╗
║ID║DATE║NAME║TYPE║
╠════════════════════════════ ════╣
║2222║1-Jan-15║史密斯║注║
║2222║2-Jan-15║琼斯║注║
║2222║2-Jan- 15║史密斯║订单║
║2222║4-Jan-15║琼斯║注║
╚══════════════════════ ══════════╝
  
 
 
解决方案
这是你可以得到一系列日期：
  SELECT DATE'2015-01-01'+ LEVEL  -  1 
 FROM dual 
 CONNECT BY DATE'2015-01-01'+ LEVEL  -  1< DATE'2015-02-01'; 
  
上述将获得2015年1月1日至2015年1月31日期间的所有日期。 / p> 
 
 
您可以使用上述方法来插入开始和结束日期并创建CTE，然后在日期中使用外连接：
  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 
  
 更新我有一段时间，我想我看到如何将上面的内容集成到您的查询中。我真的没有一个SQL小提琴的样本数据（你有很多表，无论如何）。这里是您可以开始的地方，这应该会得到所有适当的访问加上访问日期范围内的每个日期（假设没有访问超过30天 - 相应调整）：
  WITH dr AS（
 SELECT LEVEL as dd FROM dual 
 CONNECT BY LEVEL< = 30  - 我的最大日期范围为30;增加，你认为合适
）
 SELECT v.visit_id，v.start_date  -  1 + dr.dd AS meets_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'）; 
  
然后我认为你的外部连接应该是从左到右的（至少如果我明白了我可能不会：
  WITH dr AS（
 SELECT LEVEL as dd FROM dual 
 CONNECT BY LEVEL< ; = 30  - 我假定最大日期范围为30;增加，如您所见，
）
 SELECT v.visit_id，v.start_date  -  1 + dr.dd AS encounter_transaction_date 
 FROM visit v CROSS JOIN dr 
 LEFT JOIN遭遇_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遭遇_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_s ys_id 
 AND i.identifier IN（
'1234'，--Smith 
'4321'--Jones **这里有一个额外的逗号！ 
）
 LEFT JOIN人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'）; 
  
 
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  ║
╚══════╩══════════╩═══════╩═══════╝

解决方案
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');


                        
这篇关于Oracle SQL循环通过日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！