Question
Write a script that declares and sets a variable thats equal to the total outstanding balance due. If that balance due is greater than $10,000.00,
Write a script that declares and sets a variable thats equal to the total outstanding balance due. If that balance due is greater than $10,000.00, the script should return a result set consisting of VendorName, InvoiceNumber, InvoiceDueDate, and Balance for each invoice with a balance due, sorted with the oldest due date first. If the total outstanding balance due is less than or equal to $10,000.00, return the message Balance due is less than $10,000.01
Which query(ies) are viable solutions for the problem statement above?
DECLARE @TotalDue money;Set @TotalDue = (Select sum(InvoiceTotal-PaymentTotal-CreditTotal) From Invoices); if @TotalDue > 10000 (select Vendors.VendorName, Invoices.InvoiceNumber, Invoices.InvoiceDueDate, (InvoiceTotal-PaymentTotal) AS Balance from Invoices INNER JOIN Vendors on Invoices.VendorID=Vendors.VendorID --ORDER by InvoiceDueDate ASC) Else Print 'Invoices pard in full' | ||
DECLARE @OutstandingBalance money DECLARE @VendorID int SET @VendorID = @@VendorID --Error: Must declare the scalar variable "@@VendorID". SET @OutstandingBalance = ( SELECT SUM(InvoiceTotal-(PaymentTotal+CreditTotal)) FROM Invoices WHERE Invoices.VendorID = @VendorID AND (InvoiceTotal-(PaymentTotal+CreditTotal)) > 10000 )--end the Select stmt IF @OutstandingBalance > 10000 BEGIN SELECT VendorName, InvoiceNumber, InvoiceDueDate, (InvoiceTotal-(PaymentTotal+CreditTotal)) as Balance FROM Invoices JOIN Vendors On Vendors.VendorID = Invoices.VendorID WHERE Invoices.VendorID = @VendorID ORDER BY InvoiceDate ASC END ELSE BEGIN PRINT 'Balance due is small enough.' END | ||
Declare @totalInvoiceDue money; select @totalinvoicedue = Sum(Invoicetotal - creditTotal - paymenttotal) from invoices where invoivetotal - credittotal - paymenttotal > 0; if @totalInvoiceDue > 10000 select vendorName, InvoiveNumber, InvoiveDueDate, InvoiveTotal - credittotal - paymenttotal AS Balance from invoives join vendors -- cross join on invoices.vendorID = vendors.vendorID where invoicetotal - credittotal - paymenttotal > - order by invoiceDueDate; else print 'Balance due is small enough'; | ||
USE AP; DECLARE @TotalDue money; SET @TotalDue = (SELECT SUM(InvoiceTotal - PaymentTotal - CreditTotal) From Invoices); If @TotalDue <10000 PRINT 'Balance due is small enough'; else PRINT 'Name' + CONVERT(varchar,@VendorName,1) 'Invoice number' +CONVERT(int,@InvoiceNumber,1) 'Due date' +CONVERT(int,@InvoiceDueDate,1) 'Balace' + CONVERT(varchar,@TotalDue,1) ORDER BY oldest_date from InvoiceDueDate | ||
DECLARE @TotalDue money; SELECT @TotalDue = SUM(InvoiceTotal - CreditTotal - PaymentTotal) FROM Invoices Where InvoiceTotal - CreditTotal - PaymentTotal > 0; IF @TotalInvoiceDue > 10000 SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoiceTotal - CreditTotal - PaymentTotal AS Balance FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 ORDER BY InvoiceDueDate ELSE PRINT 'Balance due is less than $10,000.01.'; | ||
Use AP DECLARE @TotalInvoiceDue money; SELECT @TotalInvoiceDue = SUM(InvoiceTotal - CreditTotal - PaymentTotal) FROM Invoices -- print @TotalInvoiceDue -- IF @TotalInvoiceDue > 10000 SELECT VendorName, InvoiceNumber, InvoiceDueDate, InvoiceTotal - CreditTotal - PaymentTotal AS Balance FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 ORDER BY InvoiceDueDate; ELSE PRINT 'Balance due is less than $10,000.01.'; | ||
DECLARE @Balance money SET @Balance = (SELECT SUM(Invoices.InvoiceTotal - (Invoices.PaymentTotal - Invoices.CreditTotal)) FROM Invoices) IF @Balance > 10000 BEGIN SELECT VendorName AS 'Vendor Name', InvoiceDueDate AS 'Due Date', InvoiceNumber AS 'Invoice#', Invoices.InvoiceTotal - (Invoices.PaymentTotal - Invoices.CreditTotal) AS 'Balance' FROM Invoices INNER JOIN Vendors ON Vendors.VendorID=Invoices.VendorID WHERE (InvoiceTotal - (Invoices.PaymentTotal - Invoices.CreditTotal)) <> 0 ORDER BY InvoiceDueDate ASC END ELSE PRINT 'Balance due is small enough.' | ||
DECLARE @OutstandingBalance money Set @OutstandingBalance = ( SELECT Top 1 (InvoiceTotal-(PaymentTotal+CreditTotal)) FROM Invoices Order By (InvoiceTotal-(PaymentTotal+CreditTotal)) desc ); -- print @OutstandingBalance; -- SELECT VendorName, InvoiceNumber, InvoiceDueDate, @OutstandingBalance as Balance FROM Invoices JOIN Vendors On Vendors.VendorID = Invoices.VendorID ORDER BY InvoiceDate |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started