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

Posted on 2014-10-28
Medium Priority
Last Modified: 2015-11-10
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.)

I created a table for holding the Date columns I need and joined it to the View on the CustomerID (actually "ParticipantId."):
  FROM [TempTables].[dbo].[WIAYouthITADates] t  RIGHT OUTER JOIN [TempTables].[dbo].[vw_WIAYouthITA] v
  ON PID = ParticipantId

Open in new window

Question by:megnin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40408681
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?

Author Comment

ID: 40409484
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.
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40409493
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 )
FROM existing_table_name
7 Extremely Useful Linux Commands for Beginners

Just getting started with Linux? Here's a quick start guide that has 7 commands that we believe will come in handy.


Author Comment

ID: 40409638
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?
LVL 69

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 40409675
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.

Author Comment

ID: 40410421
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.  ;-)

Author Comment

ID: 40412050
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.

Author Closing Comment

ID: 41221449
(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.

Featured Post

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question