| Author |
 Topic  |
|
|
Crima
Starting Member
USA
17 Posts |
 Posted - 12/03/2012 : 13:46:28
|
Can someone look this over and give me feed back, any and all help would be great! thank you so much
-Crima
--1.Write a script that declares and sets a variable that’s 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 $10,000.00, return the message “Balance due is less than $10,000.00.”
select v.vendorname,
i.invoicenumber,
i.invoicedate,
SUM(i.invoicetotal - i.paymenttotal - i.credittotal) balance,
CASE
WHEN SUM(i.invoicetotal - i.paymenttotal - i.credittotal) < 10000 THEN 'Balance due is less than $10,000.00.'
ELSE Null
END Message
from vendors v
join invoices i on v.vendoris = i.vendorid
Group By
v.vendorname,
i.invoicenumber,
i.invoicedate
Order By
i.invoicedate DESC
--2.
--The following script uses a derived table to return the date and invoice total of the earliest
--invoice issued by each vendor. Write a script that generates the same result set but uses a
--temporary table in place of the derived table. Make sure your script tests for the existence of
--any objects it creates.
SELECT VendorName, InvoiceDate AS MIN_InvoiceDate, InvoiceTotal
FROM Invoices oINNER JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE EXISTS (SELECT *
FROM Invoices i
WHERE o.VenorID = i.VendorID
GROUP BY VendorID
HAVING o.InvoiceDate = MIN(i.InvoiceDate))
ORDER BY VendorName, InvoiceDate
3. --Create a stored procedure named spBalanceRange that accepts three optional parameters.
--The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance
--for each invoice with a balance due, sorted with largest balance due first. The parameter
--@VendorVar is a mask that’s used with a LIKE operator to filter by vendor name.
--@BalanceMin and @BalanceMax are parameters used to specify the requested range of
--balances due. If called with no parameters, the procedure should return all invoices with a
--balance due.
Create Proc spBalanceRange
@VendorVar VarChar(40) = '%',
@BalanceMin money = null,
@BalanceMax money = null
AS
IF @BalanceMin IS Null
Select @BalanceMin = Min(InvoiceTotal )
From Invoices
Select @BalanceMax = Max(InvoiceTotal )
From Invoices Join Vendors
On Invoices.VendorID = Vendors.VendorID
Where (InvoiceTotal >= @BalanceMin) AND
(Vendorname Like @VendorVar)
Select @BalanceMax = Max(InvoiceTotal )
From Invoices Join Vendors
On Invoices.VendorID = Vendors.VendorID
Where (InvoiceTotal >= @BalanceMin) AND
4. --Code three calls to the procedure created in problem 3 as follows:
--(a) pass the parameters by position with @VendorVar='Z%' and no balance range
--(b) pass the parameters by name with @VendorVar omitted and a balance range from $200
--to $1000
--(c) pass the parameters by position with a balance due that’s less than $200 filtering for
--vendors whose names begin with C or F.
--5.
--Create a scalar-valued function named fnUnpaidInvoiceID that returns the InvoiceID of
--the earliest invoice with an unpaid balance. Test the function in the following SELECT
--statement:SELECT VendorName, InvoiceNumber, InvoiceDueDate,
InvoiceTotal - CreditTotal - PaymentTotal AS Balance
--FROM Vendors JOIN Invoices
-- ON Vendors.VendorID = Invoices.VendorID
--WHERE InvoiceID = dbo.fnUnpaidInvoiceID()
CREATE FUNCTION fnUnpaidInvoiceID()
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @InvoiceID INT;
SET @InvoiceID = (SELECT TOP 1 InvoiceID
FROM Invoices
WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0
ORDER BY InvoiceDate);
RETURN (@InvoiceID);
END
-- Use the FUNCTION called fnUnpaidInvoiceID().
USE AP
GO
SELECT VendorName, InvoiceNumber, InvoiceDueDate,
InvoiceTotal - PaymentTotal - CreditTotal AS Balance
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceID = dbo.fnUnpaidInvoiceID()
--1.Write a script that generates the same result set as the code shown in problem 2, but
--uses a view instead of a derived table. Also write the script that creates the view. Make
--sure that your script tests for the existence of the view. The view doesn’t need to be
--redefined each time the script is executed.
2.
--Write a script that uses dynamic SQL to return a single column that represents the
--number of rows in the first table in the current database. The script should automatically
--choose the table that appears first alphabetically, and it should exclude tables named
--dtproperties and sysdiagrams. Name the column CountOfTable, where Table is the
--chosen table name.
--Hint: Use the sys.tables catalog view
select t0.name, CountOfTable =sum(rows)
from sys.partitions p
inner joinsys.tables t0
on p.object_id= t0.object_id
inner join(
select top 1 * from sys.tables t
where t.name !='dtproperties'
and t.name !='sysdiagrams'
order by t.name
) t1 on t0.object_id= t1.object_id
group by t0.name
3.
--Create a stored procedure named spDateRange that accepts two parameters,
--@DateMin and @DateMax, with data type varchar and default value null. If called with
--no parameters or with null values, raise an error that describes the invalid syntax. If
--called with non-null values, validate the parameters. Test that the literal strings are valid
--dates and test that @DateMin is earlier than @DateMax. If the parameters are valid,
--return a result set that includes the InvoiceNumber, InvoiceDate, InvoiceTotal, and
--Balance for each invoice for which the InvoiceDate is within the date range, sorted with
--earliest invoice first.
USE AP
GO
ALTER PROCEDURE spDateRange
@DateMin VARCHAR(20) = NULL,
@DateMax VARCHAR(20) = NULL
AS
-- Declare Error Variables.
DECLARE @NullDateError INT,
@BadDateError INT,
@MinDateLargerThanMaxDateError INT
-- Initialize Error Variables.
SELECT
@NullDateError = 1,
@BadDateError = 2,
@MinDateLargerThanMaxDateError = 3
-- Test for one or both values containing NULL. IF either value
-- contains NULL, there is no way to continue this stored procedure.
-- If any of the following errors occurs, the stored procedure stops.
IF @DateMin IS NULL OR @DateMax IS NULL
RETURN @NullDateError
IF ISDATE(@DateMin) = 0 OR ISDATE(@DateMax) = 0
RETURN @BadDateError
IF CAST(@DateMin AS DATETIME) > CAST(@DateMax AS DATETIME)
RETURN @MinDateLargerThanMaxDateError
-- If you've gotten this far, then the MIN/MAX dates are good.
IF NOT EXISTS (SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
(InvoiceTotal - CreditTotal - PaymentTotal) AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax AND
(InvoiceTotal - CreditTotal - PaymentTotal) > 0
ORDER BY InvoiceDate)
RAISERROR ('Invalid something here', 11, 1))
--4. --Code a call to the stored procedure created in exercise 3 that returns invoices with an
--InvoiceDate between April 10 and April 20, 2008. This call should also catch any errors
--that are raised by the procedure and print the error number and description.
IF (@QuantityOrdered < (SELECT QuantityOnHand
FROM Inventory
WHERE PartID = @PartOrdered) )
BEGIN
-- SQL statements to update tables and process order.
END
ELSE
BEGIN
-- SELECT statement to retrieve the IDs of alternate items
-- to suggest as replacements to the customer.
END Report Abuse
Com
--5. Create a table-valued function named fnDateRange, similar to the stored procedure of
--exercise 3. The function requires two parameters of data type smalldatetime. Don’t
--validate the parameters. Return a result set that includes the InvoiceNumber,
--InvoiceDate, InvoiceTotal, and Balance for each invoice for which the InvoiceDate is
--within the date range. Invoke the function from within a SELECT statement to return
--those invoices with InvoiceDate between April 10 and April 20, 2008.
A witty student |
|
|
shilpash
Yak Posting Veteran
72 Posts |
Posted - 12/03/2012 : 15:11:18
|
fOR #1---
SELECT v.vendorname
,i.invoicenumber
,i.invoicedate
,SUM(i.invoicetotal - i.paymenttotal - i.credittotal) BalDue
INTO #Baldue
FROM vendors v
JOIN invoices i
ON v.vendoris = i.vendorid
GROUP BY v.vendorname
,i.invoicenumber
,i.invoicedate
ORDER BY i.invoicedate DESC
DECLARE @Due INT= (SELECT BalDue
FROM #Baldue
)
IF @Due > 10000
SELECT *
FROM #Baldue
ELSE
PRINT 'Balance due is less than $10,000.00'
DROP TABLE #Baldue
|
Edited by - shilpash on 12/03/2012 15:12:18 |
 |
|
| |
Topic |
|
|
|
Help!
