Hi,

I have this sql

Select 
 DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName, 
 s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType, 
 ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
 ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode, 
 ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,
 p.dbProgRvw,
 ISNULL(s.dbSchDate, '1899-12-30') AS dbSchDate,
 ISNULL(s.dbSchTypeCnt, '0') AS dbSchTypeCnt,
 p.age, 
 ISNULL(rc.dbRecDate, '1899-12-30') AS dbRecDate,
 ISNULL(rc.dbRecType, '-') AS dbRecType,
 ISNULL(rc.dbRecCom, '-') AS dbRecCom, 
 a.dbPatApptTime as LastVisitDate,
 a.dbSchTypeDesc as LastVisitDesc 
 FROM
 Patient p 
 LEFT OUTER JOIN vw_ReferralKPIs r ON p.dbpatid = r.dbPatID AND r.ClientRef = p.ClientRef
 LEFT OUTER JOIN SchDetail s on s.dbPatCnt = p.dbPatCnt AND s.ClientRef = p.ClientRef
 LEFT OUTER JOIN SchTypes t on s.dbSchTypeCnt = t.dbSchTypeCnt AND t.ClientRef = p.ClientRef
 LEFT OUTER JOIN Recalls rc on p.dbpatcnt = rc.dbpatcnt AND rc.ClientRef = p.ClientRef
 LEFT OUTER JOIN Appointments a on p.dbpatcnt = a.dbpatcnt AND rc.ClientRef = a.ClientRef AND dbPFStatus = 1 
 Where  
 (r.dbStatusDesc IN ('') OR '' = '')
 AND s.dbSchDate <= GetDate()
 AND p.ClientRef = 'EPS'
 AND r.dbStatusDesc != 'Discharged' 
 AND r.dbStatusDesc != 'TC Disch'
 AND r.dbStatusDesc != 'Discharge FTA'

I've added the Appointments join code as we need LastVisitDate and LastVisitDesc but we only wish to bring back the row from Appointments that has the max date (latest date).

If I add a temp Where clause 'AND p.dbPatCnt = 9678', it brings back 12 rows (all that person's appointments where dbPFStatus =1) I only want to bring back the row that has the latest date.

thanks, 

;With cte As
(Select 
 DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName, 
 s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType, 
 ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
 ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode, 
 ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,
 p.dbProgRvw,
 ISNULL(s.dbSchDate, '1899-12-30') AS dbSchDate,
 ISNULL(s.dbSchTypeCnt, '0') AS dbSchTypeCnt,
 p.age, 
 ISNULL(rc.dbRecDate, '1899-12-30') AS dbRecDate,
 ISNULL(rc.dbRecType, '-') AS dbRecType,
 ISNULL(rc.dbRecCom, '-') AS dbRecCom, 
 a.dbPatApptTime as LastVisitDate,
 a.dbSchTypeDesc as LastVisitDesc, 
 Row_Number() Over(Partition By p.dbPatID Order By a.dbPatApptTime Desc) As rn
 FROM
 Patient p 
 LEFT OUTER JOIN vw_ReferralKPIs r ON p.dbpatid = r.dbPatID AND r.ClientRef = p.ClientRef
 LEFT OUTER JOIN SchDetail s on s.dbPatCnt = p.dbPatCnt AND s.ClientRef = p.ClientRef
 LEFT OUTER JOIN SchTypes t on s.dbSchTypeCnt = t.dbSchTypeCnt AND t.ClientRef = p.ClientRef
 LEFT OUTER JOIN Recalls rc on p.dbpatcnt = rc.dbpatcnt AND rc.ClientRef = p.ClientRef
 LEFT OUTER JOIN Appointments a on p.dbpatcnt = a.dbpatcnt AND rc.ClientRef = a.ClientRef AND dbPFStatus = 1 
 Where  
 (r.dbStatusDesc IN ('') OR '' = '')
 AND s.dbSchDate <= GetDate()
 AND p.ClientRef = 'EPS'
 AND r.dbStatusDesc != 'Discharged' 
 AND r.dbStatusDesc != 'TC Disch'
 AND r.dbStatusDesc != 'Discharge FTA')
Select 
 dbPatID, dbpatfirstname, dbPatLastName, 
 SchDetailType, SchTypeType, 
 dbStatusDesc,
 dbSchTypeCode, 
 dbSchTypeDesc,
 dbProgRvw,
 dbSchDate,
 dbSchTypeCnt,
 age, 
 dbRecDate,
 dbRecType,
 dbRecCom, 
 LastVisitDate,
 LastVisitDesc
 From cte
 Where rn = 1;

Tom

• Proposed as answer by Jason A Long 10 hours 46 minutes ago

I've found this similar problem/solution but am unclear on how to apply it to my code

http://stackoverflow.com/questions/21254760/joining-with-max-date-from-table

Following on from that link I now have...

SELECT DISTINCT
        p.dbPatID,
        p.dbpatfirstname,
        p.dbPatLastName,
        s.dbSchTypeCnt AS SchDetailType,
        t.dbSchTypeCnt AS SchTypeType,
        ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
        ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
        ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,
        p.dbProgRvw,
        ISNULL(s.dbSchDate, '1899-12-30') AS dbSchDate,
        ISNULL(s.dbSchTypeCnt, '0') AS dbSchTypeCnt,
        p.age,
        ISNULL(rc.dbRecDate, '1899-12-30') AS dbRecDate,
        ISNULL(rc.dbRecType, '-') AS dbRecType,
        ISNULL(rc.dbRecCom, '-') AS dbRecCom,
        -- New columns
        ca.LastVisitDate,
        ca.LastVisitDesc
FROM    Patient p
LEFT OUTER JOIN vw_ReferralKPIs r ON p.dbpatid = r.dbPatID
                                     AND r.ClientRef = p.ClientRef
LEFT OUTER JOIN SchDetail s ON s.dbPatCnt = p.dbPatCnt
                               AND s.ClientRef = p.ClientRef
LEFT OUTER JOIN SchTypes t ON s.dbSchTypeCnt = t.dbSchTypeCnt
                              AND t.ClientRef = p.ClientRef
LEFT OUTER JOIN Recalls rc ON p.dbpatcnt = rc.dbpatcnt
                              AND rc.ClientRef = p.ClientRef
LEFT OUTER JOIN ( SELECT    MAX(a.dbPatApptTime) AS LastVisitDate,
                            MAX(a.dbSchTypeDesc) AS LastVisitDesc,
                            a.dbpatcnt
                  FROM      appointments a
                  WHERE     a.dbPFStatus = 1
                            AND a.clientref = 'EPS'
                  GROUP BY  a.dbpatcnt
                ) ca ON ca.dbpatcnt = p.dbpatcnt
WHERE   --(
          --r.dbStatusDesc IN ( '' )
          --OR '' = ''
        --) AND 
        s.dbSchDate <= GETDATE()
        AND p.ClientRef = 'EPS'
        AND r.dbStatusDesc != 'Discharged'
        AND r.dbStatusDesc != 'TC Disch'
        AND r.dbStatusDesc != 'Discharge FTA';

This brings back the expected row count and correct LastVisitDate but the LastVistDesc is wrong. I've tried it without the Max and included it in the Group By but this produces a lot of duplicate rows.

Look closely at Tom's code, because more often than not, a CTE/Row_number combination runs faster than a subquery.  It may look radically different to what you're accustomed to, but it's probably very close to the answer you're looking for.

If you want to proceed with your version of the the revised code, you'd need to add something like this:

LEFT OUTER JOIN Appointments a 
          on p.dbpatcnt = a.dbpatcnt
          AND rc.ClientRef = a.ClientRef AND dbPFStatus = 1 
          AND dbPatApptTIme = 
                (Select Max(A2.dbpatappttime) from Appointments A2
                   where A2.clientRef = A.ClientRef and A2.dbPFStatus = 1)

If it's possible that more than one row has the maximum appointment time, then your left join would pull in all those rows.  If that's the case, you have a little more work to do, you need some way for that final query to show just the very last appointment to consider.

Hi Mattech13,

As mentioned above, a CTE&ROW_NUMBER() combination is usually the very elegant way to handle this kind of requirement. You can look into Tom's.

In any case, you prefer to use the MAX way, you can reference the below.

LEFT OUTER JOIN ( 
			SELECT a1.dbPatApptTime  AS LastVisitDate, 
			a1.dbSchTypeDesc AS LastVisitDesc, 
			a1.dbpatcnt 
			FROM appointments a1 
				JOIN
			(
			SELECT MAX(a.dbPatApptTime) dbPatApptTime,
                   a.dbpatcnt
                  FROM      appointments a
                  WHERE     a.dbPFStatus = 1
                            AND a.clientref = 'EPS'
                  GROUP BY  a.dbpatcnt
			) a2	 
			ON a1.dbpatcnt=a2.dbpatcnt AND a1.dbPatApptTime=a2.dbPatApptTime
                
				) ca ON ca.dbpatcnt = p.dbpatcnt


--or you may try another flavor with NOT EXISTS
LEFT OUTER JOIN ( 
			SELECT a1.dbPatApptTime  AS LastVisitDate, 
			a1.dbSchTypeDesc AS LastVisitDesc, 
			a1.dbpatcnt 
			FROM appointments a1 
			WHERE NOT EXISTS
				(
				SELECT 1 FROM appointments WHERE dbpatcnt=a1.dbpatcnt AND dbPatApptTime>a1.dbPatApptTime
				)
               ) ca ON ca.dbpatcnt = p.dbpatcnt

If you have any question, feel free to let me know.

in your last join , 

LEFT OUTER JOIN Appointments a 
on p.dbpatcnt = a.dbpatcnt 
AND rc.ClientRef = a.ClientRef 
AND dbPFStatus = 1
And Appointmentdate in (
Select Max(a2.Appointmentdate) From Appointments a2
where a2.dbpatcnt = a.dbpatcnt 
group by (a2.your key)
)