sql - SQL query to return all columns of max date for group by but exclude records where a different column for that group is not null

Question

For my example data set in the image below, I need to return only records for the MAX DueDate while Grouping on CompletedCertificationChecklist_Id. But if there is data in the CompletedDate, filter that CompletedCertificationChecklist_Id out.

The report will show me only open records (not completed) with the most recent DueDate

Table Query

SELECT [Id_CertificationHandsOnAssesment]
      ,[CompletedCertificationChecklist_Id]
      ,[DueDate]
      ,[CompletedDate]
FROM [sccCertificationHandsOnAssesments]

标签： sqlsql-servertsql

Answer 1

if I understand correctly :

select * from (
  select * 
    , row_number() over (partition by CompletedCertificationChecklist_Id order by DueDate desc) rn 
    , max(case when CompletedDate is not null then 1 else 0 end) over (partition by CompletedCertificationChecklist_Id) IsCompleted
from sccCertificationHandsOnAssesments
) t
where IsCompleted = 0 and rn = 1