I'm really stuck with this one, in my head it seems like a simple task.
Language T-SQL Platfom: SQL Server 2005
I have read access to a view of a table via a linked server setup, a copy of some of the column headings is located in tbl_LOSPT and stored within a database I have full access to.
The problem is the table gets appended and not updateed to throughtout the day for example;
On admission our patient administration system (PAS) automatically fires a trigger to append the record to the table stay_history this generates a stay_id number unique to the patients stay Lets say 1000. Now lets say the patient gets there care plan entered in the the PAS system instead of updating the orignal stay_id row (1000) it adds a new record (row) but issues the same stay_id
I am unable to make or get made any changes to the database I have a view from.
To move the records from the view of stay_history into tbl_LOSPT in the order the events happend but only if the update was of certain columns
stay_history on the linked server contains the following
So I want to append all new records where last_update > last_run_date (stored in log table) and event_code = 'NA' -- This bit is fine no problem but...
now I want to update the records where view.stay_id = tbl_LOSPT.stay_id but only if columns PDD,EDD,Ward,Consulatnt,Specialty have changed I DO NOT want the record if any other colomns have changed so in the above example I want to append Row 1 first, then I want to update that row with row 3 then row 5
I am using a trigger on tbl_LOSPT to copy the current version of the row to tbl_audit_LOSPT before update. Notice that the event code is the same on any of the records I want to update but not always also last_update is sometimes the same and sometimes not.
I know I am way off the mark with this code but;
UPDATE BRAVO.WSHDischargeTracking.dbo.vw_extract_stay_his tory_all dt INNER JOIN
dbo_tbl_LOSPT ON dt.stay_id = dbo_tbl_LOSPT.stay_id
SET dbo_tbl_LOSPT.planned_discharge_date = dt.planned_discharge_date, dbo_tbl_LOSPT.discharge_date = dt.discharge_date;
Any help and example code would be very gratfully received!