March 24, 2015 at 5:48 am
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
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
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'
and I want to join another table, Appointments a on a.dbPatCnt and a.ClientRef as well to include a.dbPatApptTime as LastVisitDate and a.dbSchTypeDesc as LastVisitDesc.
however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'
So the stand alone sql could be....
Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc
I'm just not sure how to incorporate that into my sql or whether there is a better way,
thanks,
March 24, 2015 at 7:01 am
Hi,
Try using Cross Apply:
SELECT
-- ...
a.LastVisitDate,
a.LastVisitDesc
FROM -- ...
CROSS APPLY
(
Select Top(1) a.dbPatApptTime as LastVisitDate, a.dbSchTypeDesc as LastVisitDesc
from appointments as a
where a.dbPFStatus = 1 and a.clientref = p.clientref
order by a.dbPatApptTime desc
) as a
WHERE -- ...
Hope this helps.
March 24, 2015 at 8:18 am
thanks.
adding just that Cross Apply code to the bottom of my existing where statement brings back the same lastvistdate and lastvisitdesc for all rows.
I think I need to be able to join with the appointments on dbpatcnt and clientref AND only get the row that has the most recent date and the desc
March 24, 2015 at 8:26 am
Complete an utter stab in the dark. . .
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
OUTER APPLY ( SELECT TOP ( 1 )
a.dbPatApptTime AS LastVisitDate,
a.dbSchTypeDesc AS LastVisitDesc
FROM appointments a
WHERE a.dbPFStatus = 1
AND a.clientref = 'EPS'
-- Join condition here, I've guessed
AND a.dbPatID = d.dbPatID
ORDER BY a.dbPatApptTime DESC
) ca
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';
If that's not what you want, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D.
It explains how to best ask a question, including how to give sample data 🙂
March 24, 2015 at 8:28 am
I've tried this - just on one dbpatcnt = 9678 and it brings back 12 rows all with different LastVisitDate and LastVisitDesc. What I require is for the sql to only bring back the row with the latest LastVisitDate.
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'
AND p.dbPatCnt = 9678
March 24, 2015 at 8:30 am
So that suggests that this is the condition you want on the apply
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
OUTER APPLY ( SELECT TOP ( 1 )
a.dbPatApptTime AS LastVisitDate,
a.dbSchTypeDesc AS LastVisitDesc
FROM appointments a
WHERE a.dbPFStatus = 1
AND a.clientref = 'EPS'
-- Join condition here, I've guessed
AND p.dbpatcnt = a.dbpatcnt AND rc.ClientRef = a.ClientRef
ORDER BY a.dbPatApptTime DESC
) ca
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';
March 24, 2015 at 8:51 am
Hi, yeah I just tried that and it works if I include the specific AND p.dbPatCnt = 9678 but the query just runs for ever without it.
March 24, 2015 at 9:19 am
I've found this similar problem/solution but am unclear on how to apply to my code
http://stackoverflow.com/questions/21254760/joining-with-max-date-from-table
March 24, 2015 at 9:29 am
mattech06 (3/24/2015)
I've found this similar problem/solution but am unclear on how to apply to my codehttp://stackoverflow.com/questions/21254760/joining-with-max-date-from-table
That is doing the same thing as what has already been suggested, pretty much. You'd write it like this: -
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';
I can't imagine that'll be faster than the previously stated query.
March 24, 2015 at 9:56 am
That's almost correct thanks! I get the expected row count and correct LastVisitDate but the LastVisitDesc is wrong.
I've tried it without MAX for LastVisitDesc and putting it into the group by instead but get a load of duplicate rows.
March 24, 2015 at 9:59 am
mattech06 (3/24/2015)
That's almost correct thanks! I get the expected row count and correct LastVisitDate but the LastVisitDesc is wrong.I've tried it without MAX for LastVisitDesc and putting it into the group by instead but get a load of duplicate rows.
Can you change it back to a TOP 1, e.g.
LEFT OUTER JOIN ( SELECT TOP 1
a.dbPatApptTime AS LastVisitDate,
a.dbSchTypeDesc AS LastVisitDesc,
a.dbpatcnt
FROM appointments a
WHERE a.dbPFStatus = 1
AND a.clientref = 'EPS'
ORDER BY a.dbPatApptTime
) ca ON ca.dbpatcnt = p.dbpatcnt
I think that is logically what you want to do.
March 24, 2015 at 10:52 am
Changing that back to TOP 1 brings back all nulls for LastVisitDate and Desc apart from one row.
I can see why I had the wrong LastVisitDesc using MAX as it works alphabetically and just brought back the one beginning with Z.
So almost there 🙂
March 25, 2015 at 2:21 am
mattech06 (3/24/2015)
Changing that back to TOP 1 brings back all nulls for LastVisitDate and Desc apart from one row.I can see why I had the wrong LastVisitDesc using MAX as it works alphabetically and just brought back the one beginning with Z.
So almost there 🙂
Change it to a MIN then?
March 25, 2015 at 2:29 am
That would just bring back the first alphabetical one and not the one that's on the same row of the maxdate one.
This does work - it seems a bit messy and the processing time isn't great but at least it's a working soln that I can build on.
LEFT OUTER JOIN (
SELECT a1.dbPatApptTime AS LastVisitDate,
a1.dbSchTypeDesc AS LastVisitType,
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
March 25, 2015 at 2:58 am
-- Note that the filter on r.dbStatusDesc in the WHERE clause
-- turns the left join on vw_ReferralKPIs into an inner join
-- because it excludes NULL values of r.dbStatusDesc in the output.
-- Either change to an INNER JOIN as shown here:
SELECT ...
FROM Patient p
INNER 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
WHERE
--(r.dbStatusDesc IN ('') OR '' = '')
AND s.dbSchDate <= GetDate()
AND p.ClientRef = 'EPS'
AND r.dbStatusDesc NOT IN ('Discharged','TC Disch','Discharge FTA')
-- or put the filter into the join:
SELECT ...
FROM Patient p
LEFT JOIN vw_ReferralKPIs r
ON p.dbpatid = r.dbPatID
AND r.ClientRef = p.ClientRef
AND r.dbStatusDesc NOT IN ('Discharged','TC Disch','Discharge FTA')
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
WHERE
--(r.dbStatusDesc IN ('') OR '' = '')
AND s.dbSchDate <= GetDate()
AND p.ClientRef = 'EPS'
-- Note also that DISTINCT will almost certainly add an expensive sort to the plan.
-- Better to investigate why dupes appear in the output and deal with it in a more targeted and deliberate manner.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply