CODEWITHSUNDEEP
t-sqlselectsubqueryssms-17
Chris
Chris

Reputation: 1

Select Top 1 Subquery in Select Statement Returning Null

I am attempting to pull 1 row per individual and need to add a column with the date of the most recent progress note for that individual, if there has been none completed, then null is correct.

Pulling the progress note date in the select statement, using a left join, creates multiple rows for some individuals if they have had more than 1 progress note.

Using select top 1 sub-query (tried this subquery in both the select statement and as an OUTER APPLY) returns only NULLS.

Is there a way to accomplish what I’m trying to do without using CTE?

Below is a sample of the code, it is not the full code. I have also limited it to pull only 1 individual for now to test that I am pulling the way intended, but the final will be pulling about 80 individuals, all with different progress note dates, so limiting based on just the date is not an option.

I have tried:

SELECT DISTINCT 
i.[ID]
    ,p.[ProblemName]    
    ,pn.[Stamp] as 'Date of Last Note'
    ,(SELECT TOP 1 pn.[Stamp]
        FROM [PROGRESS_NOTES] pn
        RIGHT JOIN [INDIVIDUALS] i on pn.[ID] = i.[ID]
        ORDER BY pn.[Stamp] ASC) as 'Date of Last Note'
    
FROM [INDIVIDUALS] i 
INNER JOIN [PROBLEMS] p ON i.[ID] = p.[ID]
LEFT JOIN [PROGRESS_NOTES] pn ON i.[ID] = pn.[ID]

And Also have tried:

SELECT DISTINCT 
    i.[ID]
        ,p.[ProblemName]    
        ,pn.[Stamp] as 'Date of Last Note'
    
FROM [INDIVIDUALS] i 
INNER JOIN [PROBLEMS] p ON i.[ID] = p.[ID]
OUTER APPLY (SELECT TOP 1 pn.[Stamp]
        FROM [PROGRESS_NOTES] pn
        RIGHT JOIN [INDIVIDUALS] i on pn.[ID] = i.[ID]
        ORDER BY pn.[Stamp] ASC) pn

The rest of the output is not included in the snip as it contains sensitive information, but all columns before the Date of Last Note column contain duplicate information across the 2 rows, the highlighted cell is the DateTime I want the report to capture

Upvotes: -1

Views: 96

Answers (1)

hennyboiszz
hennyboiszz

Reputation: 16

A few things to try fixing 1st snippet not sure if it’ll work)

Remove the distinct, left join individuals to problems, for the date column lookup the is null function.

It’ll go something like this for the date column.

Isnull((
select top(1) pn.Stamp 
from progress_notes pn 
where pn.id = i.id 
order by pn.stamp), NULL) as DateOfLastNote

Since it’s an in-line select statement you can actually just use the i value to lookup the date. If there are no dates then the isnull function will go and put the date value as null

Upvotes: 0

Related Questions