Here are my 2 tables. SUBMISSION_ASSGNMENT links to WF_STATUS_ASSIGNMENT by Assignment_ID
There is one submission_id with 2 assignments. Each assignments has 3 statuses.
How do I get the MAX(DATE_START) from all the statuses under the submission and the info attached to it(status_id, WF_STATUS_TYPE_CODE)
From the 6 rows (2x3) I need only one at the end.
I craked it..
SELECT NEWPART.*, RECDESC.wf_status_type_desc AS "Recommedation"
(SELECT sa.submission_id, sa.assignment_id, MAX(wsa.DATE_START) as DATE_START1
FROM nhpsas.submission_assignment sa, nhpsas.wf_status_assignment wsa
WHERE wsa.flag_active = 1 and sa.assignment_id = wsa.assignment_id and sa.role_type_code in (14,19)
GROUP BY sa.submission_id, sa.assignment_id) WSA1) TOPPART
, MAX(wsa.DATE_START) as DATE_START1
FROM nhpsas.submission_assignment sa join nhpsas.wf_status_assignment wsa on sa.assignment_id = wsa.assignment_id
WHERE wsa.flag_active = 1 and sa.role_type_code in (14,19)
GROUP BY sa.submission_id
) WSA1) LOWPART
ON TOPPART.submission_id = lowpart.submission_id and TOPPART.DATE_START1 = LOWPART.DATE_START1) NEWPART
ON WSA.ASSIGNMENT_ID = NEWPART.ASSIGNMENT_ID
INNER JOIN nhpsas.wf_status_type_assignment_v RECDESC
ON WSA.WF_STATUS_TYPE_CODE = RECDESC.WF_STATUS_TYPE_CODE
WHERE RECDESC.interface_code = 1