bawpie bawpie - 1 month ago 12
SQL Question

Returning nearest date to date in a different table

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


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.

Edit: 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.

Edit: 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

Answer

You can use analytics:

SELECT *
  FROM (SELECT d.customer_id, d.doc_id, d.doc_start, d.doc_end, e.event_id,
                row_number() over(PARTITION BY d.doc_id 
                                  ORDER BY abs(d.doc_start - e.event_start)) rn
           FROM doc d
           JOIN event e ON d.customer_id = e.customer_id
        /*WHERE CLAUSE*/)
 WHERE rn = 1
Comments