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