I\'m trying to write a sql server 2016 code for my class and I\'m stuck. I need
ID: 3864858 • Letter: I
Question
I'm trying to write a sql server 2016 code for my class and I'm stuck. I need help. Attached are the instructions, you should be able to give me an outline on what to do.
Write a SELECT statement that returns the following: Vendor me. Vendor InvoiceID, Invoice Sequence, and InvoiceLineltemAmount for each invoice that has more than one line item in the InvoiceLineltems table. Sort the results by Vendorstate. Be sure your output has a row for each sequence number for each invoice -Hint: use a subquery that looks for Invoicesequence 1; Additional hint: you will need to join 3 tables as part of your main select statement.Explanation / Answer
Select Tbl3.VendorName,Tbl3.VendorState
,Tbl4.InvoiceID,Tbl4.InvoiceSequence,Tbl4.InvoiceLineItemAmount
From Vendor Tbl3
Inner Join
(
Select Tbl1.*
From
(
/* do the needful join for vendor and invoice fields*/
select Invoice.InvoiceID,InvoiceLinetems.InvoiceSequence, InvoiceLinetems.InvoiceLineItemAmount,InvoiceLinetems.VendorID
From Invoice Inner Join InvoiceLinetems
On Invoice.InvoiceID=Invoice.InvoiceID
)as Tbl1
Inner Join
(
/*Group by Invoice ID’s and find Invoice ID’s listed more than once*/
Select InvoiceID
From InvoiceLinetems
Group by InvoiceID
Having Count(*)>1
)as Tbl2
On Tbl1.InvoiceID=Tbl2.InvoiceID
)Tbl4
On Tbl3.VendorID=Tbl4.VendorID
Order by Tbl3.VendorState
/*If you find any issue with query provide me table structure*/