I have a query that results in one applicationid being associated with multiple dates. I want to get my query to count all the occurrences of the applicationid as one count for the purposes of reporting

The code is currently:

SELECT 
    ap.ApplicationId,
    ql.AssignedDate,
    ql.UnassignedDate,
    ql.CompletedDate,
    d.CompletedDate AS FinalDate
FROM
    Application ap
JOIN 
    QueueLog ql ON ap.ApplicationId=ql.ApplicationId
ORDER BY 
    ql.AssignedDate

Current results are:

The desired output is to count as one every occurrence of the ApplicationId in the Queuelog table that meets the criteria of

ql.AssignedDate <= GETDATE()
AND (ql.CompletedDate IS NULL OR ql.UnassignedDate IS NULL) 
AND d.CompletedDate IS NULL

EXPECTED RESULT The occurence which has the most recent date for the appIds that has multiple occurences and those that have one occurence to return that value i.e |ApplicationId| AssignedDate| UnassignedDate| CompletedDate| FinalDate| |:--------------:|:------------: |:----------------:|:-------------:|---------| |2765201| 2022-02-25 09:55:28.210| NULL| NULL| NULL| |2765201| 2022-02-25 09:55:28.167| NULL| NULL| NULL| to return the first line only

CodePudding user response：

Try out this SQL Query:

SELECT ap.ApplicationId, COUNT(ap.ApplicationId) AS ApplicationCount FROM Application ap JOIN QueueLog ql ON ap.ApplicationId=ql.ApplicationId where ql.AssignedDate <= GETDATE() AND (ql.CompletedDate IS NULL OR ql.UnassignedDate IS NULL) AND ql.CompletedDate IS NULL
GROUP BY ap.ApplicationId

CodePudding user response：

You can use row_number to assign a priority to each repeating group and select only the most recent:

select 
    ApplicationId,
    AssignedDate,
    UnassignedDate,
    CompletedDate,
    FinalDate
from (
    select 
        ap.ApplicationId,
        ql.AssignedDate,
        ql.UnassignedDate,
        ql.CompletedDate,
        d.CompletedDate as FinalDate,
        Row_Number() over(partition by ApplicationId order by AssignedDate desc) rn
    from Application ap
    join QueueLog ql on ap.ApplicationId=ql.ApplicationId
)t
where rn=1
order by AssignedDate