0

I am trying to figure out how I can add a new column to a derived table in a view without modifying the underlying tables. Let me explain what I have to do:

I need to add a simplified MQSA module to an existing RIS application that runs on SQL Server. I need to display a list of patients who's lay letters for breast MRI are due for mailing, which happens every 11 months. Once an item on the list is printed, it needs to fall off the list until it is due again.

In order to do this, I need an extra column that records the last print date. If the number of days between the current date and the last printed date is greater than 334 days, it needs to show on the list.

My problem is that I am not allowed to modify the existing database so I must have this column in a new table, inside a separate database. I have created a view in this separate database with tables inside it from the existing database that gives me a list of unfiltered data in the right format.

I have added a table to the new database that records the relevant IDs and the last printed date when items on the list are printed. I have added this new table into the view with a left outer join to one of the tables and checked the last date printed column so as to be part of the resulting list.

I cannot figure out how to bring the new table together with the view to display only lay letters that are due for printing. The new table of course is empty, no data in it yet because nothing has been printed yet. Just out of curiosity, I have manually replicated the data (two columns of ids) into the new table to test if it would work and it did, it worked fine as expected.

If I could somehow replicate the data from the view into this new table, that would automatically update itself to reflect changes in the view, I could accomplish my task easily like so:

SELECT 
    PersonID, 
    PatientID, 
    ModalityID, 
    ReportID, 
    FirstName, 
    LastName, 
    HomePhone, 
    WorkPhone,
    LastPrintDate, 
    CONVERT(VARCHAR(10), SignedDate, 101) AS SignedDate 
FROM 
    mqsa.dbo.vw_mq_LayLetterAlerts_Derived 
WHERE (ModalityID = 4) 
    AND (GETDATE() >= DATEADD(day, 334, LastPrintDate))

I don't know much about triggers so I don't know if that would be an option considering that I am working with a view that has multiple tables in it.

Any ideas would be appreciated. I just need to know in what direction I should start my research.

Thanks!

EDIT: Perhaps my question should be how to mirror a table in another database?

user1108996
  • 275
  • 2
  • 4
  • 13

2 Answers2

1

Why don't you just join to the view in the old database?

select <cols>
from olddb..vw_OLDVIEW left outer join
     newdb..PrinterTable
     on <whatever>
where <filters>

You can wrap this in a view in your new database as well.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I already have -- it says so in my post. Perhaps I am misunderstanding what you are saying. – user1108996 Apr 03 '13 at 22:02
  • @user1108996 . . . Iw was responding to this statement "If I could somehow replicate the data from the view into this new table, that would automatically update itself to reflect changes in the view, I could accomplish my task easily like so:" You don't need to replicate the data, you can just use the view. – Gordon Linoff Apr 03 '13 at 22:03
  • The tables in the view are dropped in from default database where all the data is -- with the exception of the one new table which contains the LastPrintDate column which is the anchor for the query. This table is empty because nothing has been printed yet. No values for the LastPrintDate means zero results displayed. If I could replicate some of the data into this table, then I could fill the LastPrintColumn with a default date from which the results could be calculated. This one table would have to update itself to reflect changes in the other tables. – user1108996 Apr 03 '13 at 22:26
0

Solved the problem by modifying my existing query to use the SignedDate if the LastPrintDate was null. Thankfully I didn't need to replicate anything or do anything exotic. The answer was in front of me the whole time and I couldn't see it.

user1108996
  • 275
  • 2
  • 4
  • 13