Calculate Adam McLean's final invoice bill for his family visit started on 2021-08-03. Adam McLean's guest...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Calculate Adam McLean's final invoice bill for his family visit started on 2021-08-03. Adam McLean's guest ID is G5 and the invoice sales tax percentage is 9%. Follow the steps below to complete this assignment. Read the entire assignment before you work on these steps. Step 1: create an invoice table with the following script (Copy and paste the script to SSMS) CREATE TABLE INVOICE ( InvoiceID InvoiceDate BookID GuestID smallint date Int varchar(4) date identity (1000,1) Primary Key, default getdate(), decimal(7,2) decimal(7,2) decimal(7,2) decimal(5,2) decimal(7,2) Not Null, Not Null, Not Null, StartDate CondoFee ActivityFee Invoice Total default 0, Sales Tax Grand Total default 0, Foreign key (BookID) REFERENCES BOOKING (BookID), Foreign key (GuestID) REFERENCES GUEST (GuestID)); default 0, default 0, default 0, Submit the Select * from invoice query result. (You should see an empty table as shown below) Results | Messages Invoice D Invoice Date BookID Guest ID StartDate Condo Fee ActivityFee Invoice Total Sales Tax Grand Total Step 2: write a select statement to calculate the condo fee for the McLean family. (15 points) Condofee = (No of days * daily rate) of the condo booked by the McLean family on 2021-08-03 1 Total Condo Fee 1015.00 Step 3: write a select statement to calculate the activity fee for the McLean family. (15 points) Activityfee = Numberinparty* PPP (McLean family had two reservations during their visit) 1 Activityfee 275.00 Step 4: Write a stored procedure, Create_Invoice, to insert an invoice record. (30 points) 1. Use TRY CATCH, Transaction Commit/Rollback to insert a new invoice record for the McLean Family. 2. The stored procedure will pass two input parameters, GuestID and StartDate, to avoid hard-coding in step 2 and 3. 3. The invoice record will be populated with invoice ID, invoice date, book ID, GuestID, Start Date, CondoFee, and ActivityFee. The rest of the attributes will contain zeros. Invoice ID and date will be automatically populated. You will have 5 attributes in your insert statement. Make sure the number and position of commas and parentheses are correct to separate these 5 in your insert statement. CREATE PROCEDURE Create_Invoice (input parameters) AS BEGIN Try begin transaction End Try Begin Catch +0 Commit transaction End Catch Enter your insert into statement here............ Select ERROR_NUMBER() AS Error Number, ERROR_MESSAGE() AS ErrorMessage; Rollback 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures Rollback End Catch +0 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures System Stored Procedures dbo.Create_Invoice Step 5: Create 1 trigger, Calculate_Invoice_Total, on the INVOICE table. (20 points) When an invoice record is inserted in step 4, this trigger will 1. Read the inserted temp table from Step 4 to get the Condofee and ActivityFee for invoice calculation 2. Use update statements to calculate and update invoice total, sales tax, and grand total. Invoicetotal = condofee + activityfee Salestax Invoicetotal *0.09 Grandtotal invoice total + sales tax = Submit the Create trigger statement and a screenshot of the created trigger under the INVOICE table folder (see example below). Create trigger Calculate_Invoice_Total Enter your trigger creation statement here.......... dbo.INVOICE Columns Keys 1 Constraints Triggers Calculate_Invoice_Total Indexes Step 6: execute the stored procedure and select the final invoice record for display. The result should look similar to the sample below. (10 points) Exec Create_Invoice 'G5', '2021-08-03'; Select * from invoice; (Your invoice number and invoice date might be different from the example below) InvoiceID Invoice Date BookID GuestID Start Date Condo Fee Activity Fee 1000 2022-07-13 168 G5 2021-08-03 1015.00 275.00 Invoice Total Sales Tax 1290.00 116.10 Grand Total 1406.10 Calculate Adam McLean's final invoice bill for his family visit started on 2021-08-03. Adam McLean's guest ID is G5 and the invoice sales tax percentage is 9%. Follow the steps below to complete this assignment. Read the entire assignment before you work on these steps. Step 1: create an invoice table with the following script (Copy and paste the script to SSMS) CREATE TABLE INVOICE ( InvoiceID InvoiceDate BookID GuestID smallint date Int varchar(4) date identity (1000,1) Primary Key, default getdate(), decimal(7,2) decimal(7,2) decimal(7,2) decimal(5,2) decimal(7,2) Not Null, Not Null, Not Null, StartDate CondoFee ActivityFee Invoice Total default 0, Sales Tax Grand Total default 0, Foreign key (BookID) REFERENCES BOOKING (BookID), Foreign key (GuestID) REFERENCES GUEST (GuestID)); default 0, default 0, default 0, Submit the Select * from invoice query result. (You should see an empty table as shown below) Results | Messages Invoice D Invoice Date BookID Guest ID StartDate Condo Fee ActivityFee Invoice Total Sales Tax Grand Total Step 2: write a select statement to calculate the condo fee for the McLean family. (15 points) Condofee = (No of days * daily rate) of the condo booked by the McLean family on 2021-08-03 1 Total Condo Fee 1015.00 Step 3: write a select statement to calculate the activity fee for the McLean family. (15 points) Activityfee = Numberinparty* PPP (McLean family had two reservations during their visit) 1 Activityfee 275.00 Step 4: Write a stored procedure, Create_Invoice, to insert an invoice record. (30 points) 1. Use TRY CATCH, Transaction Commit/Rollback to insert a new invoice record for the McLean Family. 2. The stored procedure will pass two input parameters, GuestID and StartDate, to avoid hard-coding in step 2 and 3. 3. The invoice record will be populated with invoice ID, invoice date, book ID, GuestID, Start Date, CondoFee, and ActivityFee. The rest of the attributes will contain zeros. Invoice ID and date will be automatically populated. You will have 5 attributes in your insert statement. Make sure the number and position of commas and parentheses are correct to separate these 5 in your insert statement. CREATE PROCEDURE Create_Invoice (input parameters) AS BEGIN Try begin transaction End Try Begin Catch +0 Commit transaction End Catch Enter your insert into statement here............ Select ERROR_NUMBER() AS Error Number, ERROR_MESSAGE() AS ErrorMessage; Rollback 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures Rollback End Catch +0 37165_stung4 Database Diagrams Tables Views External Resources Synonyms Programmability Stored Procedures System Stored Procedures dbo.Create_Invoice Step 5: Create 1 trigger, Calculate_Invoice_Total, on the INVOICE table. (20 points) When an invoice record is inserted in step 4, this trigger will 1. Read the inserted temp table from Step 4 to get the Condofee and ActivityFee for invoice calculation 2. Use update statements to calculate and update invoice total, sales tax, and grand total. Invoicetotal = condofee + activityfee Salestax Invoicetotal *0.09 Grandtotal invoice total + sales tax = Submit the Create trigger statement and a screenshot of the created trigger under the INVOICE table folder (see example below). Create trigger Calculate_Invoice_Total Enter your trigger creation statement here.......... dbo.INVOICE Columns Keys 1 Constraints Triggers Calculate_Invoice_Total Indexes Step 6: execute the stored procedure and select the final invoice record for display. The result should look similar to the sample below. (10 points) Exec Create_Invoice 'G5', '2021-08-03'; Select * from invoice; (Your invoice number and invoice date might be different from the example below) InvoiceID Invoice Date BookID GuestID Start Date Condo Fee Activity Fee 1000 2022-07-13 168 G5 2021-08-03 1015.00 275.00 Invoice Total Sales Tax 1290.00 116.10 Grand Total 1406.10
Expert Answer:
Related Book For
Fundamentals of Law Office Management
ISBN: 978-1133280842
5th edition
Authors: Pamela Everett Nollkamper
Posted Date:
Students also viewed these databases questions
-
We are a small law firm in New York City with 2 attorneys (our entrepreneurial leader and founder) and 2 committed and dedicated support staff and more on the way! We are a family law firm. Its all...
-
Follow the steps below to complete the assignment: Workbook: Begin by creating a new workbook. In the workbook you will want to have 2 worksheets. Name your worksheets as follows: Sheet 1: Monthly...
-
The payroll project that follows is the online version of the same project you completed manually in Chapter 7 of your text. For this project, you will use the Cengage Learning General Ledger to...
-
Find the area between the parabolas y = 2x 2 + 1 and y = x 2 + 5.
-
Sketch the graph of y = 1/(x + 1) and then find the equation of the tangent line at (1, ) (see Example 3).
-
how to use the true population standard deviation, compute and interpret a 95% confidence interval on the average return of startups invested in by the venture capital firm
-
Stanley Neal and Helen Jobe each have equity of $40,000.00 in an existing partnership. The partners share equally in all changes in equity. On August 1 of the current year, the existing partners...
-
On July 1, 2010, Jessie Halverson established an interior decorating business, Photogenic Designs. During the month, Jessie Halverson completed the following transactions related to the business:...
-
Which apps do you see yourself using the least? Why?
-
8) A pesticide inhibits the activity of a particular enzyme A, which can therefore be used to assay for the presence of the pesticide in an unknown sample. a. In the laboratory, the initial rate data...
-
Monette Company manufactures car seats in its Seattle plant. Each car seat passes through the assembly department and the testing department. This problem focuses on the assembly department. (Click...
-
Let's use this space to share and collaborate. As you start this module and go through the assignments, visit this area and address all or any of the questions listed below. Tie your input to one or...
-
Factor u - 9u-2u + 18 by grouping.
-
6. Policy coordination and the world economy Consider an open economy in which the real exchange rate is fixed and equal to one. Consumption, investment, gov- ernment spending, and taxes are given by...
-
Solve log 12(x+5)= log 4(6x). Provide your answer below: x=
-
Last week, as part of your Assignment, you proposed a process, policy, or legal change to address a legal or ethical issue in criminal investigation. You return to that change here by discussing it...
-
2. c) 6.0 Evaluate these integrals: a) 6; S 11 dx S x+1' 5x+11 x+2x-3 d) dx; 2xdx x +1' h) nx dx; b) 3 dx e) 1 (2441 x+1' S ln x dx. e x lnx dx; f) x-2x- dx; (x-1)
-
Why is a help desk and production support critical to system implementations? Discuss its interrelationship with the problem management and reporting system.
-
It all came apart in less than a year: a booming products liability boutique headed by an ex-partner from a big firmthe scion of one of Marylands most prominent legal families, an author, and a...
-
Margaret Cleary is a paralegal who works for a law firm that requires her to bill clients seven hours a day. One of Margarets assignments was to prepare interrogatories on a real estate case. She...
-
Norma Hunter, a paralegal, works for the law firm of Everett & Mullins, a respected medium-sized law firm. Her supervising attorney is Rick Everett, who specializes in criminal defense work. Norma...
-
A materials purchases journal, a cash payments journal, and a general journal for Vanyo, Inc., a manufacturer of cardboard boxes, are provided in the Working Papers. Work independently to complete...
-
Work sheets for Bedthings, Inc., a manufacturing firm, are included in the Working Papers. Use the information for April from Work Together 20-2. Work independently to complete the following...
-
Perry, Inc., completed the following factory cost transactions during August of the current year: Instructions: 1. Journalize the transactions. Use page 4 of a materials purchases journal and page 8...
Study smarter with the SolutionInn App