sql-servermaxminms-project-server-2013

SQL: Find MIN but greater than a MAX


I am working with task history and trying to find two dates attached to the same record: 1) Most recent time a task was approved (max approve); 2)The first submitted date after said approval.

Here is what I have so far:

Select  
a.assn_uid,
max(b.ASSN_TRANS_DATE_ENTERED) as LastApprove, 
e.LastSubmitted 

FROM [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS] a
inner join [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS] b
on a.ASSN_TRANS_UID = b.ASSN_TRANS_UID



join (select c.assn_uid,
min(d.ASSN_TRANS_DATE_ENTERED) as LastSubmitted

FROM [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS] c
inner join [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS] d
on c.ASSN_TRANS_UID = d.ASSN_TRANS_UID

where c.ASSN_UID = '499879BC-28B2-E411-8B0A-00059A3C7A00'
and d.[ASSN_TRANS_COMMENT_TYPE_ENUM] = 0

group by c.assn_uid ) e
on e.ASSN_UID = a.ASSN_UID


where a.ASSN_UID = '499879BC-28B2-E411-8B0A-00059A3C7A00'
and b.[ASSN_TRANS_COMMENT_TYPE_ENUM] = 1

group by a.assn_uid, e.LastSubmitted

This is close, however, it gives me the first time ever that the task was submitted. I am sure that I need to use another subquery, I just dont know how to reference a column within the same result.

Here is the task history. Highlighted are the two dates I am trying to show: enter image description here


Solution

  • With some help, we figured out we needed an additional min wrapped around the query.

    SELECT
           final.assn_uid,
           final.LastApprove,
           min(final.SubmissionDate) FirstSubmitted
    FROM
           (Select  
           a.assn_uid,
           max(b.ASSN_TRANS_DATE_ENTERED) as LastApprove, 
           e.SubmittedDates SubmissionDate
    
           FROM [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS] a
           inner join [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS] b
           on a.ASSN_TRANS_UID = b.ASSN_TRANS_UID
    
    
    
           join (select c.assn_uid,
           (d.ASSN_TRANS_DATE_ENTERED) as SubmittedDates
    
           FROM [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS] c
           inner join [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS] d
           on c.ASSN_TRANS_UID = d.ASSN_TRANS_UID
    
           where c.ASSN_UID = '499879BC-28B2-E411-8B0A-00059A3C7A00'
           and d.[ASSN_TRANS_COMMENT_TYPE_ENUM] = 0
    
           ) e
           on e.ASSN_UID = a.ASSN_UID
    
    
           where a.ASSN_UID = '499879BC-28B2-E411-8B0A-00059A3C7A00'
           and b.[ASSN_TRANS_COMMENT_TYPE_ENUM] = 1
           and e.SubmittedDates > b.ASSN_TRANS_DATE_ENTERED
    
           group by a.assn_uid, e.SubmittedDates) Final
    
    GROUP BY
           final.assn_uid,
           final.LastApprove