gamblsj
asked on
SQL 2008
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.
USE AP
SELECT VendorName, FirstInvoiceDate, InvoiceTotal
FROM Invoices JOIN
(SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
FROM Invoices
GROUP BY VendorID) AS FirstInvoice
ON (Invoices.VendorID = FirstInvoice.VendorID AND
Invoices.InvoiceDate = FirstInvoice.FirstInvoiceD ate)
JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
ORDER BY VendorName, FirstInvoiceDate
This where I have gotten:
USE AP
IF OBJECT_ID('#VendorTotal') IS NOT NULL
DROP Table #VendorTotal
SELECT MIN(InvoiceDate) AS FirstInvoiceDate, InvoiceTotal
INTO #VendorTotal
FROM Invoices
GROUP BY InvoiceTotal
SELECT Vendorname
FROM Vendors JOIN #VendorTotal
ON Vendors.VendorID = #VendorTotal.VendorID
GROUP BY VendorName
This is the last question of six questions, and I have been stuck on it for several hours. I seldom ask for help, but this one has me stumped.
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.
USE AP
SELECT VendorName, FirstInvoiceDate, InvoiceTotal
FROM Invoices JOIN
(SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
FROM Invoices
GROUP BY VendorID) AS FirstInvoice
ON (Invoices.VendorID = FirstInvoice.VendorID AND
Invoices.InvoiceDate = FirstInvoice.FirstInvoiceD
JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
ORDER BY VendorName, FirstInvoiceDate
This where I have gotten:
USE AP
IF OBJECT_ID('#VendorTotal') IS NOT NULL
DROP Table #VendorTotal
SELECT MIN(InvoiceDate) AS FirstInvoiceDate, InvoiceTotal
INTO #VendorTotal
FROM Invoices
GROUP BY InvoiceTotal
SELECT Vendorname
FROM Vendors JOIN #VendorTotal
ON Vendors.VendorID = #VendorTotal.VendorID
GROUP BY VendorName
This is the last question of six questions, and I have been stuck on it for several hours. I seldom ask for help, but this one has me stumped.
ASKER
I made the change (below) bot it runs with out any resultant rows. The JOIN appears to be the problem, but I am having no luck debugging.
IF OBJECT_ID('tempdb..#Vendor Total') IS NOT NULL
DROP Table #VendorTotal
SELECT MIN(InvoiceDate) AS FirstInvoiceDate, InvoiceTotal
INTO #VendorTotal
FROM Invoices
GROUP BY InvoiceTotal
SELECT Vendorname
FROM Vendors JOIN #VendorTotal
ON Vendors.VendorID = #VendorTotal.VendorID
GROUP BY VendorName
IF OBJECT_ID('tempdb..#Vendor
DROP Table #VendorTotal
SELECT MIN(InvoiceDate) AS FirstInvoiceDate, InvoiceTotal
INTO #VendorTotal
FROM Invoices
GROUP BY InvoiceTotal
SELECT Vendorname
FROM Vendors JOIN #VendorTotal
ON Vendors.VendorID = #VendorTotal.VendorID
GROUP BY VendorName
IF OBJECT_ID('tempdb..#Vendor Total') IS NOT NULL
DROP Table #VendorTotal
SELECT MIN(InvoiceDate) AS FirstInvoiceDate, VendorID
INTO #VendorTotal
FROM Invoices
GROUP BY VendorID
SELECT Vendors.VendorID, Vendorname, InvoiceTotal
FROM Vendors JOIN #VendorTotal
ON Vendors.VendorID = #VendorTotal.VendorID and Vendors.InvoiceDate = #VendorTotal.FirstInvoiceD ate
DROP Table #VendorTotal
SELECT MIN(InvoiceDate) AS FirstInvoiceDate, VendorID
INTO #VendorTotal
FROM Invoices
GROUP BY VendorID
SELECT Vendors.VendorID, Vendorname, InvoiceTotal
FROM Vendors JOIN #VendorTotal
ON Vendors.VendorID = #VendorTotal.VendorID and Vendors.InvoiceDate = #VendorTotal.FirstInvoiceD
ASKER
It seems close, but it now generates the following error:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'InvoiceDate'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'InvoiceTotal'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'InvoiceDate'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'InvoiceTotal'.
SELECT Vendors.VendorID, Vendorname, InvoiceTotal
FROM Invoices JOIN #VendorTotal
ON Invoices.VendorID = #VendorTotal.VendorID and Invoices.InvoiceDate = #VendorTotal.FirstInvoiceD ate
JOIN Vendors ON Vendors.VendorID = Invoices.VendorID
FROM Invoices JOIN #VendorTotal
ON Invoices.VendorID = #VendorTotal.VendorID and Invoices.InvoiceDate = #VendorTotal.FirstInvoiceD
JOIN Vendors ON Vendors.VendorID = Invoices.VendorID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you cyberkiwi. This problem was stumping my beginner's acumen.
Temporary tables reside in the tempdb, and the existence check must be done as follows:
IF OBJECT_ID('tempdb..#Vendor
DROP Table #VendorTotal