I have provided my current code, the step 3 should be the continuation.

Step 3: Create Stored Procedure to Process Project Delays

You will create the SQL Script to create procedures to insert/ update data and process a project delay

- SP_ProcessProjectDelay: Given a project Id, this procedure finds if any max end date of any activity within the project is after the projects projected end date. If this happens, the procedure will calculate how many days it is late (use DATEDIFF) and fines the project $1050 for each day late it is late. In addition, the project tables projectedenddate will be updated with the new end date and the fundedbudget will be updated with the original funded budget plus the fines per day late.

o Parameters: projectId.

Example: The Falcon Coaster has a ProjectId AA01 has a projected end date of 6/30/2017. It has 2 activities: ActivityId: AA90 ActivityName: Build Coaster EndDate: 6/01/2017 ActivityId: AA91 ActivityName: Inspect Coster EndDate: 7/30/2017 Since Activity AA91 ends 30 days after the projected end date of the project, the project will have an additional $31,500 (30 X $1050) added to the fundedbudget columns original value. Also, the projects new projected end date will be 7/30/17

create table ProjectMain ( projectId char(4) NOT NULL PRIMARY KEY, projectName varchar(50) NOT NULL, firmFedID char(9) NOT NULL , fundedbudget decimal(16,2) NOT NULL , projectStartDate date NOT NULL , projectStatus varchar(25) , projectTypeCode char(5) NOT NULL, projectedEndDate date NOT NULL, projectManager char(8) NOT NULL, ) GO

Create table Firm ( firmFedID char(9) NOT NULL PRIMARY KEY, firmName varchar(50) NOT NULL, firmAddress varchar(50) NOT NULL, ) GO

Create table ActivityMain ( activityId char(4) NOT NULL PRIMARY KEY, activityName varchar(50) NOT NULL, costToDate decimal(16,2) NOT NULL, activityStatus varchar(25), startDate date NOT NULL, endDate date NOT NULL, ) GO

CREATE TABLE Activity ( projectId char(4), ActivityId char(4), PRIMARY KEY(projectId, ActivityId) ) GO

Create table ProjectType ( projectTypeCode char(5) NOT NULL PRIMARY KEY, projectTypeDesc varchar(50), ) GO

Create table ProjectBilling ( projectBillID char(6), TransAmount decimal(16,9), TransDesc varchar(255), TransDate datetime, projectID char(4), accountMgr char(8), Constraint PR_ProjectBillID UNIQUE(ProjectBillID) ) GO

CREATE PROCEDURE SP_AddUpdateProjBill @projectBillID CHAR(6), @TransAmount DECIMAL(16,9), @TransDesc VARCHAR(255), @TransDate DATETIME, @projectId CHAR(4), @accountMgr CHAR(8) AS BEGIN IF NOT EXISTS(SELECT projectBillID FROM ProjectBilling WHERE projectBillID = @projectBillID) BEGIN INSERT INTO ProjectBilling VALUES (@projectBillID, @TransAmount, @TransDesc, @TransDate, @projectId, @accountMgr) END ELSE BEGIN UPDATE ProjectBilling SET TransAmount = @TransAmount, TransDesc = @TransDesc, TransDate = @TransDate, projectID = @projectId, accountMgr = @accountMgr WHERE projectBillID = @projectBillID END END GO

CREATE PROCEDURE SP_DeleteProjectBill @projectBillID CHAR(6) AS BEGIN DELETE FROM ProjectBilling WHERE projectBillID = @projectBillID END GO