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 hsdSET hsd.LASTSEENDT = lsd.LastSeenDateFROM 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 - 2012-06-30 : 00:56:35
