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
33 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:ScottPletcher
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
 
LVL 1

Author Comment

by:megnin
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
(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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now