Add Columns for input to a View or Table based on parent tables that must not be changed.

We have a customer database created by a 3dr party, so we can't modify the tables.

I need to create a web application to enter dates for some new milestones that are not represented in the existing tables.

We've brought the other relevant customer information from several tables into a single View.

"StartDate" and "AnticipatedEndDate" are existing columns in the customer database and our View.  I need to add three new columns, say, "Phase1CompletedDate", "Phase2CompletedDate", and "Phase3CompletedDate" so we can enter dates in these fields as needed an create a report showing them, but not modify the parent database in any way, only our View or Table derived from the parent db.

One of the parent tables and our View contains a unique "CustomerID" for linking everything together.

The Question:
How do I create and updateable View or Table with my three new Date columns and the CustomerID column to associate the date entries to the customer?  
(Still can't modify the parent db or tables.  Can't add the columns to the parent db/Table.)  
My SQL knowledge is pretty basic.

Below is the View definition as reference:
SELECT     RTRIM(st.Lastname) + ', ' + RTRIM(st.Firstname) AS staffer, RTRIM(p1.Lastname) + ', ' + RTRIM(p1.Firstname) AS client, SUBSTRING(p1.Ssn, 6, 4) AS last4ssn, 
                      p1.ParticipantId, p2.ProviderGasId AS ProviderMIPID, '' AS ProviderName, p3.Name AS ProgramName, a2.StartDate AS ProgramStart, a2.AnticipatedEndDate, 
                      CASE WHEN a1.actualenddate > getdate() THEN NULL ELSE a1.actualenddate END AS ActualEndDate
FROM         gazelle.dbo.Account AS a1 INNER JOIN
                      gazelle.dbo.AccountTraining AS a2 ON a1.AccountId = a2.Account_AccountId INNER JOIN
                      gazelle.dbo.Participant AS p1 ON a1.Participant_ParticipantId = p1.ParticipantId INNER JOIN
                      gazelle.dbo.Program AS p3 ON a1.Program_ProgramId = p3.ProgramId INNER JOIN
                      gazelle.dbo.Provider AS p2 ON a2.Provider_ProviderId = p2.ProviderId INNER JOIN
                      gazelle.dbo.[User] AS st ON a1.User_UserId = st.UserId
WHERE     (a1.Fundsource_FundsourceId = '86F543FB-EBE1-4317-ABEE-6544048967B0') AND (a2.StartDate IS NOT NULL)

Open in new window


I just need to add three new columns for date entries.  I'll be creating the web application for entering the date values in ASP.Net/VB.Net 4.0 and old ADO.Net.  The report with conditional formatting to show "overdue" dates will be in SSRS 2008 R2.  

Other goal information:  A "Phase1Date" may become overdue as long as there is no entry in that column.  Once a date is entered, then it's considered complete and any "overdue flag" would be cleared.  I just include that for "full idea" completeness.

Thanks for any assistance. (I've already done the Google search.  Please, no links to Google Searches unless it's complete, clear and relevant.)

Edit:
I created a table for holding the Date columns I need and joined it to the View on the CustomerID (actually "ParticipantId."):
SELECT [PID]
		,v.ParticipantId
      ,[EmployabilitySkillsDate]
      ,[ESUpdatedDate]
      ,[ESUpdatedBy]
      ,[WorkExperienceDate]
      ,[WEUpdatedDate]
      ,[WEUpdatdBy]
      ,[PlacementServicesDate]
      ,[PSUpdatedDate]
      ,[PSUpdatedBy]
  FROM [TempTables].[dbo].[WIAYouthITADates] t  RIGHT OUTER JOIN [TempTables].[dbo].[vw_WIAYouthITA] v
  ON PID = ParticipantId

Open in new window

LVL 1
megninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You've found the solution, create a separate table to hold the new dates.  If necessary, you can even use a separate db; in theory you could use a separate schema, but be careful if you do, because that will break ownership "chaining" and thus could cause you security issues.

You can UPDATE that table just as you would any other SQL table, since that's all it is.  You can use a view to combine data from it with the other tables, just as you've done before.

What do you still have questions/concerns with?
0
megninAuthor Commented:
I don't know how to "import" or update the CustomerID field from the parent table so the new table with the date columns can be joined with the View.  If I know how to "populate" the CustomerID field in my new table, then I can "link" the dates to the correct records.  I hope I'm explaining my problem/ignorance ;-) correctly.
0
Scott PletcherSenior DBACommented:
Do you just need to insert each unique row as a place-holder/"shell" so the dates can be updated later?:

INSERT INTO new_table ( ParticipantId, NewDate1, NewDate2, NewDate3 )
SELECT DISTINCT v.ParticipantId, NULL, NULL, NULL
FROM existing_table_name
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

megninAuthor Commented:
Um, I think so.  The basic problem I'm trying to solve is adding the three date columns and then later putting values into them to track progress of "customers" that are in a database that I can't add columns to.  So I need to insert their ID into my new table, create the new columns and then create an SSRS report on the whole thing.  

Just like you have above, my new table need only have the ID to link them and then my new columns, so I can update my new table as the dates need to be changed, but the parent table is never written to.  Only my new table with the ID to join them.  I can do it like you have above "on demand" or "manually", but is there a way to keep my new table updated with the current ID's?
0
Scott PletcherSenior DBACommented:
Yes.  You could use an INSERT trigger on the base table to do a corresponding insert on the other table.

Or you just INSERT a row on the other table whenever you need to, if a corresponding row doesn't exists, and UPDATE the row if it does.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
Ahhhhhhh!  I was trying to do a trigger on the View.  Thanks.  I think that will do the trick.    I've really never used a trigger before.  Learning something new every day.  ;-)
0
megninAuthor Commented:
Arrrrg.  This is getting complicated.  ;-)  Is it possible to put a condition in the trigger that references multiple tables in the database?  I need the condition on which the Insert Trigger fires to match the WHERE clause of the view in my original question post.
0
megninAuthor Commented:
(I forgot about this question. Sorry)  What I ended up doing was very much like that.  I created a new table for the updatable columns and use a T-SQL MERGE expression whenever the web application is loaded.
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.