This is the question that I have to answer. I have to have the same exact output

Question

This is the question that I have to answer. I have to have the same exact output as below. I have to create a procedure that calculates total cost for each print job. Below are the ERD and tables. I need this in Microsoft Sql. I am using Sql Server 2012 and I need it in SQl, not Oracle.

Print Job ID Print Term Stu Tech ID PrintID Print Count Total Print Amount Print Date Spring2017 12345678 0.16 2017-01-10 Spring 2017 12345678 2 10 0.70 2017-01-25 Spring 2017 23456789 1 2017-01-15 100 4.00 2017-01-25 Spring 2017 23456789 2.00 Spring2017 34567890 3 1.25 2017-01-10 Fall2016 12345678 3 2.50 2016-11-10 7 Spring 2017 34567890 3 6 1.50 2017-04-10 When you print your papers, you see the total pages you are printing and the total cost for that job. Please create a procedure that will help us find the total cost for each print job you sent. Use printID and jobID as an input Hint: Use Prints and PrintTransactions table Test using the following steps to view the shown (output) PrintTransactions table: Step 1: Insert the following row INSERT INTO PrintTransactions values (7, Spring2017 ,34567890,3,6,0, 2017/04/10 Step 2: Execute the procedure you created Step 3: Use the following Select command: Select from PrintTransactions

Explanation / Answer

CREATE PROCEDURE dbo.Trans_Total @JobID int , @PrintID int
AS

DECLARE
@Total float
begin
SELECT @Total = print_unit_cost*print_count
FROM PRINT AS A INNER JOIN PRINTTRANSACTIONS AS B ON A.PRINTID=B.PRINTID AND A.PRINTID=@PrintID AND b.PRINT_JOB_ID=@JobID
UPDATE PRINTTRANSACTIONS SET TOTAL_PRINT_AMOUNT=@Total WHERE PRINTID=@PrintID AND PRINT_JOB_ID=@JobID
END

EXEC dbo.Trans_Total 7,3

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.