Help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help!

Author

Topic

Crima
Starting Member

17 Posts

Posted - 2012-12-03 : 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
Posting Yak Master

103 Posts

Posted - 2012-12-03 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources