asked on

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

Scott Pletcher

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?

David Megnin

ASKER

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.

Scott Pletcher

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

David Megnin

ASKER

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?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Megnin

ASKER

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. ;-)

David Megnin

ASKER

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.

David Megnin

ASKER

(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.