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!
hsd.LASTSEENDT = lsd.LastSeenDate
SELECT MAX(ds.act_date) As LastSeenDate,
FROM dim_patient pt
fact_transac ft ON pt.PtPK = ft.TrnFKPtPk
dim_date ds ON ft.TrnServiceDtId = ds.date_id
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
ON hsd.MRN = lsd.PtMrn