Return Most Recent Time

I have a complex join procedure where I need to return the most recent time, on X date from X Patient.

SELECT  CONVERT(varchar(36), apt.UniqueID), 
        apt.atime, 
        RTRIM(apt.apwork) + ' ' + RTRIM(apt.apwrk2),
        aps.apsdispchar,
        RTRIM(LTRIM(aps.apstextcolor)),
        RTRIM(LTRIM(aps.apsbgcolor)),
        apT.apid,
        dbo.MakeCaseString(pat.pfname, pat.pfnamcase) + ' ' + dbo.MakeCaseString(pat.plname, pat.plnamcase),
        apn.apnentrytime
        FROM apt INNER JOIN pat ON pat.pid = apt.apid INNER JOIN aps on ((apt.aconfstat IS NOT NULL AND apt.aconfstat = aps.apsid) OR (apt.aconfstat IS NULL AND aps.apsid = ' ')) INNER JOIN apn ON (apn.apnpid = apt.apid AND apn.apndate = apt.adate AND apn.apntime = apt.atime)
WHERE apt.adid = @ProviderIDParam   AND apt.adate = @DateParam AND apn.apnentrytime IN 开发者_开发技巧(SELECT MAX(apn.apnentrytime) FROM apn)
ORDER BY apt.atime ASC

Currently I'm using:

apn.apnentrytime IN (SELECT MAX(apn.apnentrytime) FROM apn)

It works for some data, while other data (that's fairly large), it wont work.

You need to correlate the subquery to the outer query on patient id (what I'm assuming is pat.pid).

Select Cast(apt.UniqueID As varchar(36))
    , apt.atime
    , RTRIM(apt.apwork) + ' ' + RTRIM(apt.apwrk2)
    , aps.apsdispchar
    , RTRIM(LTRIM(aps.apstextcolor))
    , RTRIM(LTRIM(aps.apsbgcolor))
    , apT.apid
    , dbo.MakeCaseString(pat.pfname, pat.pfnamcase) + ' ' + dbo.MakeCaseString(pat.plname, pat.plnamcase)
    , apn.apnentrytime
From apt 
    Inner Join pat 
        ON pat.pid = apt.apid 
    Inner Join aps 
        On ((apt.aconfstat Is Not Null And apt.aconfstat = aps.apsid) 
            Or (apt.aconfstat IS NULL And aps.apsid = ' ')) 
    Inner Join apn 
        On (apn.apnpid = apt.apid 
            And apn.apndate = apt.adate 
            And apn.apntime = apt.atime)
Where apt.adid = @ProviderIDParam   
    And apt.adate = @DateParam 
    And apn.apnentrytime IN (   
                            Select Max(APN1.apnentrytime) 
                            From apn As APN1
                                Join apt As APT1
                                    On APT1.apid = APN1.apnpid
                                        And APT1.adate = APN1.apndate
                                        And APT1.atime = APN1.apntime
                            Where APT1.apid = pat.pid
                            )
Order By apt.atime ASC