Return a MAX(DATE_START) from a child table and a parent table

Transact-SQL
1

image
image709×386 46.5 KB

Here are my 2 tables. SUBMISSION_ASSGNMENT links to WF_STATUS_ASSIGNMENT by Assignment_ID
CASE example:
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.

2

I craked it..

SELECT NEWPART.*, RECDESC.wf_status_type_desc AS "Recommedation"
FROM (

SELECT TOPPART.*
FROM (
SELECT WSA1.*
FROM
(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
INNER JOIN
(select WSA1.*
FROM
(
SELECT sa.submission_id
, 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
INNER JOIN
NHPSAS.WF_STATUS_ASSIGNMENT wsa
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

3 
select 
        submission_id
      , max(date_start) 
from 
     table 
group by 
      submission_id