Databases
StackOverflow2013
Postsdbo
POSplitting one table based on criteria and comparing
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSplitting one table based on criteria and comparing
    primarykey
    data
    text
    <p>I'm not quite sure on the best way to phrase this particular query, so I hope the title is adequate, however, I will attempt to describe what it is I need to be able to understand how to do. Just to clarify, this is for oracle sql.</p> <p>We have a table called assessments. There are different kinds of assessments within this table, however, some assessments should follow others in a logical order and within set time frames. The problems come in when a client has multiple assessments of the same type, as we have to use a fairly inefficient array formula in excel to identify which 'full' assessment corresponds with the 'initial' assessment. </p> <p>I have an earlier query that was resolved on this site (<a href="https://stackoverflow.com/questions/8615286/returning-relevant-date-from-multiple-tables-including-additional-table-info-or">Returning relevant date from multiple tables including additional table info</a>) which I believe includes a lot of the logic for what is required (particularly in identifying a corresponding event which has occurred within a specified timeframe). However, whilst that query pulls data from 3 seperate tables (assessments, events, responsiblities), I now need to create a query that generates a similar outcome but pulling from 1 main table and a 2nd table to return worker information. I thought the most logical way would be be to create a query that looks at the assessment table with one type of assessment, and then joins to the assessment table again (possibly a temporary table?) with assessment type that would follow the initial one.</p> <p>For example:</p> <p>Table 1 (Assessments):</p> <pre><code>Client ID Assessment Type Start End P1 1 Initial 01/01/2012 05/01/2012 </code></pre> <p>Table 2 (Assessments temp?):</p> <pre><code>Client ID Assessment Type Start End P1 2 Full 12/01/2012 </code></pre> <p>Table 3:</p> <pre><code>ID Worker Team 1 Bob Team1 2 Lyn Team2 </code></pre> <p>Result:</p> <pre><code>Client ID Initial Start Initial End Initial Worker Full Start Full End P1 1 01/01/2012 05/01/2012 Bob 12/01/2012 </code></pre> <p>So table 1 and table 2 draw from the same table, except it's bringing back different assessments. Ideally, there'd be a check to make sure that the 'full' assessment started within X days of the end of the 'initial' assessment (similar to the 'likely' check in the previous query mentioned earlier). If this can be achieved, it's probably worth mentioning that I'd also be interested in expanding this to look at multiple assessment types, as roughly in the cycle a client could be expected to have between 4 or 5 different types of assessment. Any pointers would be appreciated, I've already had a great deal of help from this community which is very valuable.</p> <p>Edit:</p> <p>Edited to include solution following MBs advice.</p> <pre><code>Select * From( Select I.ASM_SUBJECT_ID as PNo, I.ASM_ID As IAID, I.ASM_QSA_ID as IAType, I.ASM_START_DATE as IAStart, I.ASM_END_DATE as IAEnd, nvl(olm_bo.get_ref_desc(I.ASM_OUTCOME,'ASM_OUTCOME'),'') as IAOutcome, C.ASM_ID as CAID, C.ASM_QSA_ID as CAType, C.ASM_START_DATE as CAStart, C.ASM_END_DATE as CAEnd, nvl(olm_bo.get_ref_desc(C.ASM_OUTCOME,'ASM_OUTCOME'),'') as CAOutcome, ROUND(C.ASM_START_DATE -I.ASM_START_DATE,0) as "Likely", row_number() over(PARTITION BY I.ASM_ID ORDER BY abs(I.ASM_START_DATE - C.ASM_START_DATE))as "Row Number" FROM O_ASSESSMENTS I left join O_ASSESSMENTS C on I.ASM_SUBJECT_ID = C.ASM_SUBJECT_ID and C.ASM_QSA_ID IN ('AA523','AA1326') and ROUND(C.ASM_START_DATE - I.ASM_START_DATE,0) &gt;= -2 AND ROUND(C.ASM_START_DATE - I.ASM_START_DATE,0) &lt;= 25 and C.ASM_OUTCOME &lt;&gt;'ABANDON' Where I.ASM_QSA_ID IN ('AA501','AA1323') AND I.ASM_OUTCOME &lt;&gt; 'ABANDON' AND I.ASM_END_DATE &gt;= '01-04-2011') WHERE "Row Number" = 1 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USbawpie
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSplitting one table based on criteria and comparing
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POSplitting one table based on criteria and comparing
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat is the condition to find the full start as in your example there is no field that matches between the tables?
      singulars
      1. POSplitting one table based on criteria and comparing
      1. USMark
    2. COSorry, there should have been a client id in the assessment table that would be used to link the assessments together, but it would also rely on there being a set amount of days between the end of the initial and the beginning of the core - i.e. a gap no more than say +/- 20 days.
      singulars
      1. POSplitting one table based on criteria and comparing
      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