Solved

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

Posted on 2014-10-28
8
47 Views
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.)

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

0
Comment
Question by:megnin
  • 5
  • 3
8 Comments
 
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?
0
 
LVL 1

Author Comment

by:megnin
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.
0
 
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 )
SELECT DISTINCT v.ParticipantId, NULL, NULL, NULL
FROM existing_table_name
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:megnin
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?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 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.
0
 
LVL 1

Author Comment

by:megnin
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.  ;-)
0
 
LVL 1

Author Comment

by:megnin
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.
0
 
LVL 1

Author Closing Comment

by:megnin
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.
Thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
This video teaches users how to migrate an existing Wordpress website to a new domain.

828 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