在其他表格中返回最近的日期 [英] Returning nearest date to date in a different table

问题描述

我有两个表，一个表称为事件"，另一个表称为文档".每个表都有两个日期列，开始和结束(因此，事件开始，事件结束，文档开始，文档结束).这两个表通过名为Customer_ID的字段链接.我有兴趣根据客户ID将最接近的相关事件开始(及其ID)日期返回到Doc开始日期，因此表中的数据可能如下所示:

I have two tables, one called Events and one called Documents. Each table has two date columns, start and end (so Event Start, Event End, Doc Start, Doc End). Both tables are linked by a field called Customer_ID. I'm interested in returning the closest relevant event start (and its ID) date to the Doc start date based on the customer ID, so for example the data in the tables might look like:

Customer_ID DOC_ID      DOC_Start   DOC_END
A           12          22/01/2011  23/01/2011
A           13          01/12/2011  05/12/2011
C           22          13/03/2011  20/03/2011


Customer_ID Event_ID    Event_Start Event_END
A           J1          01/01/2011  23/01/2011
A           J2          04/12/2011  05/12/2011
C           J44         15/03/2011  20/03/2011

我希望最终结果显示出来:

I'd want the final result to show this:

Customer_ID DOC_ID  DOC_Start   DOC_END     Event_ID
A           12      22/01/2011  23/01/2011  J1
A           13      01/12/2011  05/12/2011  J2
C           22      13/03/2011  20/03/2011  J44

我尝试使用谷歌搜索解决方案，并尝试了一个或两个建议，但我找不到的示例似乎都不是用于比较表中的日期，甚至不是Oracle.另外，我对SQL的了解还很有限，但是我所学到的大多数都来自这里，因此，非常感谢我已经获得的帮助.

I've tried googling for a solution, and tried one or two suggestions but none of the examples I can find seem to be for comparing dates across tables, or even for Oracle. Also, I have fairly limited knowledge when it comes to SQL, but most that I have picked up has been from here, so thanks already for the assistance I've already received.

另一个条件是，我只想返回相关的事件/文档类型.因此，我只想带回DOC_TYPE为查询"或信息"的DOC_ID，Event_Type的也是如此.

A further condition is that I'd like to return correlating event/doc types only. So I only want to bring back DOC_ID's where the DOC_TYPE is 'Enquiry' or 'Info', and the same goes for the Event_Type's.

谢谢文森特，我把建议的位置放进去了(想想我已经尝试过了，但是在那里！)，现在我得到了所需的结果.

Thanks Vincent, I put the where in as suggested (think I'd tried it everywhere but there!) and I'm now getting the results required.

如果有人感兴趣，我将发布工作解决方案以供将来参考:

I'll post the working solution for future reference if anyone is interested:

SELECT
*

FROM (SELECT 
O_ASSESSMENTS.ASM_SUBJECT_ID as "ID", 
O_ASSESSMENTS.ASM_ID as "Assessment ID", 
O_ASSESSMENTS.ASM_START_DATE as "Assessment Start",  
O_ASSESSMENTS.ASM_END_DATE as "Assessment End", 
O_SERVICE_EVENTS.SEV_ID as "Event ID", 
O_SERVICE_EVENTS.SEV_ACTUAL_DATE as "Event Start", 
O_SERVICE_EVENTS.SEV_OUTCOME_DATE as "Event End",
ROUND(O_ASSESSMENTS.ASM_START_DATE -O_SERVICE_EVENTS.SEV_ACTUAL_DATE,0) as "Likely",
  row_number() over(PARTITION BY  O_ASSESSMENTS.ASM_ID                                    
ORDER BY abs(O_ASSESSMENTS.ASM_START_DATE -  O_SERVICE_EVENTS.SEV_ACTUAL_DATE)) rn            
FROM 
O_ASSESSMENTS 
JOIN  O_SERVICE_EVENTS ON O_ASSESSMENTS.ASM_SUBJECT_ID = O_SERVICE_EVENTS.SEV_SUBJECT_ID
Where O_SERVICE_EVENTS.SEV_CODE IN ('t','t1') AND O_ASSESSMENTS.ASM_QSA_ID IN ('test','test1')  )  WHERE rn = 1 

推荐答案

您可以使用 查看全文