Databases
StackOverflow2013
Postsdbo
POReturning nearest date to date in a different table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturning nearest date to date in a different table
    primarykey
    data
    text
    <p>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:</p> <pre><code>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 </code></pre> <p>I'd want the final result to show this:</p> <pre><code>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 </code></pre> <p>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.</p> <p>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. </p> <p>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.</p> <p>I'll post the working solution for future reference if anyone is interested:</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USbawpie
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POReturning nearest date to date in a different table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POReturning nearest date to date in a different table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan an `event_start` date come BEFORE the `DOC_start` state and still be considered the closest? Or does the `event_start` always have to be later?
      singulars
      1. POReturning nearest date to date in a different table
      1. USean5533
    2. COYes, it could do. :)
      singulars
      1. POReturning nearest date to date in a different table
      1. USbawpie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload