Okay, I'm a little stumped on how to (efficiently) do this. I need to update a table, setting a date field to a "Date of previous visit" that basically looks at a record, takes a "Date of Service", and finds the patient's most recent Date of Service that occured prior to it. The first issue I'm having is the error:
"hsd.ITSVCDAT could not be bound" which I understand is tied to the HAVING clause. Commenting this out (for testing) causes it to spin for a really really long time...I finally stopped it after about an hour of running and there are only 81 records to update. I tried writing his using joins instead of a subquery, but it still takes forever.
It seems like a pretty straightforward query...could the fact that most of the tables reside on a different server than the one I'm connected to? The code is pasted below. Please help!
UPDATE hsd
SET
hsd.LASTSEENDT = lsd.LastSeenDate
FROM
Demographics hsd
INNER JOIN
(
SELECT MAX(ds.act_date) As LastSeenDate,
pt.PtMrn
FROM dim_patient pt
INNER JOIN
fact_transac ft ON pt.PtPK = ft.TrnFKPtPk
INNER JOIN
dim_date ds ON ft.TrnServiceDtId = ds.date_id
INNER JOIN
fact_sched fs ON pt.PtPK = fs.SchFkPtPk AND ds.date_id = fs.SchDateId
GROUP BY pt.PtMrn
HAVING MAX(ds.act_date) < hsd.ITSVCDAT
) lsd
ON hsd.MRN = lsd.PtMrn
Multiserver update query
Posted - 06/30/2012 : 00:56:35
