从多个表返回相关日期,包括其他表信息 [英] Returning relevant date from multiple tables including additional table info
问题描述
This is continuing on from a query that was solved previously via stackoverflow (Returning nearest date to date in a different table), however, I now wish to develop it a little further.
我拥有的SQL是这样的:
The SQL I have is this:
SELECT *
FROM (SELECT O_ASSESSMENTS.ASM_SUBJECT_ID as "P Number",
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"
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 ('ICS_E3','CPINVEST') AND
O_ASSESSMENTS.ASM_QSA_ID IN ('AA1329','AA521') )
WHERE "Row Number" = 1
基本上,我们有两个表-o_assessments和o_service_events,并且此SQL将最近的服务事件返回到评估中.现在,我想在查询中包含一些更复杂的信息,以使其对最终用户更有帮助-即团队名称和工作人员名称.
Basically, we have two tables - o_assessments and o_service_events, and this SQL is returning the nearest service event to the assessment. Now I want to include some more contexual information into the query to make it more helpful for the end user - namely the team name and the worker name.
不幸的是,团队和工作人员又在另一个表中(o_职责),并且通过asm_id与res_rec_id链接到o_assessments.
Unfortunately, team and worker are in a different table again (o_responsibilities), and are linked to the o_assessments by asm_id to res_rec_id.
问题是,我真的不确定如何在上述sql中调用此表-因此,任何建议都将不胜感激!
Problem is, I'm really not sure how to call this table into the above sql - so any advice would really be appreciated!
我还想知道,是否有可能调整现有查询以仅在可能"字段的差为0(因为更大的值可能与该特定评估无关)时才返回相应事件.我知道我可以将其添加到where,但是如果我添加该行
I was also wondering if it would be possible to tweak the existing query to only return corresponding events when the 'likely' field had a difference of 0 (as anything greater is probably not related to that particular assessment). I know I can add it into the where, but if I add the line
and "Likely" = 0
它仅返回带有事件的评估,不会突出显示任何问题(即没有相应事件的评估).
it only returns assessments with an event, which wouldn't highlight any issues (i.e. assessments without corresponding events).
我一直在使用SQL进行学习,但是目前有很多事情似乎真的超出了我的范围,因此,任何建议都将不胜感激!我不确定是要修改原始查询还是开始新查询,所以希望我不会无意间违反任何规则.
I'm learning all the time with SQL, but there are a lot of things that just seem really beyond me at the moment, so any advice is much appreciated! I wasn't sure whether to amend my original query or to start a new one, so I hope I haven't infringed on any rules inadvertently.
好的,按照Mark的解决方案,这就是我的工作.
Okay, following Mark's solution, this is what I have working.
SELECT
*
FROM
(SELECT
OAS.ASM_SUBJECT_ID as "P Number",
OAS.ASM_ID as "Assessment ID",
OAS.ASM_START_DATE as "Assessment Start",
OAS.ASM_END_DATE as "Assessment End",
OAS.ASM_AUTH_DATETIME as "Authorisation Date",
nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'') as "Outcome",
nvl(olm_bo.get_org_name(ORE.RES_PARTY_OUN_ID),'') as "Team",
nvl(olm_bo.get_per_name(ORE.RES_PARTY_ID),'') as "Worker",
OSE.SEV_ID as "Event ID",
OSE.SEV_ACTUAL_DATE as "Event Start",
OSE.SEV_OUTCOME_DATE as "Event End",
ROUND(OAS.ASM_START_DATE -OSE.SEV_ACTUAL_DATE,0) as "Likely",
row_number() over(PARTITION BY OAS.ASM_ID
ORDER BY
abs(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE))as "Row Number"
FROM O_ASSESSMENTS OAS
INNER JOIN O_RESPONSIBILITIES ORE ON OAS.ASM_ID = ORE.RES_REC_ID
AND ORE.RES_PARTY_OUN_ID = 'TEAM'
LEFT JOIN O_SERVICE_EVENTS OSE ON OAS.ASM_SUBJECT_ID = OSE.SEV_SUBJECT_ID
AND
OSE.SEV_CODE IN ('EVENT')
AND
ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) >= -7
AND
ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) <= 7
Where OAS.ASM_QSA_ID IN ('ACODE')
AND
nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'') <> 'Abandon' ) WHERE "Row Number" = 1
推荐答案
尝试:
SELECT *
FROM (SELECT OAS.ASM_SUBJECT_ID as "P Number",
OAS.ASM_ID as "Assessment ID",
OAS.ASM_START_DATE as "Assessment Start",
OAS.ASM_END_DATE as "Assessment End",
ORE.TEAM,
ORE.WORKER,
OSE.SEV_ID as "Event ID",
OSE.SEV_ACTUAL_DATE as "Event Start",
OSE.SEV_OUTCOME_DATE as "Event End",
ROUND(OAS.ASM_START_DATE -OSE.SEV_ACTUAL_DATE,0) as "Likely",
row_number() over(PARTITION BY OAS.ASM_ID
ORDER BY abs(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE))as "Row Number"
FROM O_ASSESSMENTS OAS
LEFT JOIN O_RESPONSIBILITIES ORE
ON OAS.ASM_ID = ORE.RES_REC_ID
LEFT JOIN O_SERVICE_EVENTS OSE
ON OAS.ASM_SUBJECT_ID = OSE.SEV_SUBJECT_ID AND
OSE.SEV_CODE IN ('ICS_E3','CPINVEST') AND
ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) = 0
Where OAS.ASM_QSA_ID IN ('AA1329','AA521') )
WHERE "Row Number" = 1
请注意,这假定RES_REC_ID是O_RESPONSIBILITIES上的唯一标识符.
Note that this assumes that RES_REC_ID is a unique identifier on O_RESPONSIBILITIES.
这篇关于从多个表返回相关日期,包括其他表信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!