Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Starting a new one is the right course of action; editing a question to "add scope" is not a good practice. This falls into "Adding scope" so a new question was the correct approach.</p> <pre><code>SELECT ID, "Assessment ID", "Assessment Start", "Assessment End", "Event ID", "Event Start", "Event End", Likely, Team_name, Worker_name 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)) as "Row Number", RES.TEAM_NAME, RES.WORKER_NAME FROM O_ASSESSMENTS INNER JOIN O_SERVICE_EVENTS ON O_ASSESSMENTS.ASM_SUBJECT_ID = O_SERVICE_EVENTS.SEV_SUBJECT_ID INNER JOIN O_Responsibilities res ON O_Assessments.ASM_ID = res.res_rec_ID WHERE O_SERVICE_EVENTS.SEV_CODE IN ('ICS_E3','CPINVEST') AND O_ASSESSMENTS.ASM_QSA_ID IN ('AA1329','AA521') ) InnerTable WHERE "Row Number" = 1 and (Likely = 0 or LIKELY is null) </code></pre> <p>All I did here was </p> <ol> <li>Add your Join criteria on the inner select statement </li> <li>Included the columns you asked for, though the names were not provided so I assumed TEAM_NAME and WORKER_NAME, on the INNER and OUTER select statements </li> <li>Eliminated the AND after the join before the where as in comment to original post</li> <li>Added an alias to table name in inner select incase joins are needed to outer table at some point. </li> <li>Added the (likely = 0 or likely is null) to outer where clause</li> <li>Reformatted SQL a bit to keep readable here.</li> </ol> <p>per 5 above, it may not quite be correct, I don't fully understand your comment about assessments not having service dates. If that is the case, the Join between assessments and services MUST be a left join, instead of an INNER, if you want ALL assessments and related services if they exist. Currently you are getting only those assessments which HAVE related services. IF you need all assessments other changes may be required. </p> <p>So...</p> <pre><code> FROM O_ASSESSMENTS INNER JOIN O_SERVICE_EVENTS ON O_ASSESSMENTS.ASM_SUBJECT_ID = O_SERVICE_EVENTS.SEV_SUBJECT_ID </code></pre> <p>would become (only INNER to LEFT was changed)</p> <pre><code> FROM O_ASSESSMENTS LEFT JOIN O_SERVICE_EVENTS ON O_ASSESSMENTS.ASM_SUBJECT_ID = O_SERVICE_EVENTS.SEV_SUBJECT_ID </code></pre> <p>and it assumes assessment always has a responsibilities record, if not a LEFT join would be needed there as well.</p> <p>Lastly, thought would need to be given as to what result you wnat for LIKELY when the SEV_Actual_Date is null. What value should likely have?</p>
    singulars
    1. This table or related slice is empty.
    1. POReturning relevant date from multiple tables including additional table info
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USxQbert
    1. USxQbert
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for this, unfortunately just finishing for the year, so might not have a chance to test it before January, but will certainly have a look - thank you especially for explaining what you have done, as this is the kind of help I need!
      singulars
      1. PO
      1. USbawpie
    2. CO@xQbert - This won't return assessments where likely is neither 0 nor null.
      singulars
      1. PO
      1. USJosef K
    3. COI'm missing something... (Likely=0 or Likely is null) should do it. However the INNER join currently prevents a NULL value and I indicated as such in my response; as I did not fully understand OPs requirement.
      singulars
      1. PO
      1. USxQbert
 

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