Avatar of gamblsj
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.FirstInvoiceDate)
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.
DatabasesMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
gamblsj

8/22/2022 - Mon
derekkromm

See previous question that you already asked and I commented on.

Temporary tables reside in the tempdb, and the existence check must be done as follows:

IF OBJECT_ID('tempdb..#VendorTotal') IS NOT NULL
    DROP Table #VendorTotal
ASKER
gamblsj

  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..#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
derekkromm

IF OBJECT_ID('tempdb..#VendorTotal') 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.FirstInvoiceDate
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
gamblsj

  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'.
derekkromm

SELECT Vendors.VendorID, Vendorname, InvoiceTotal
FROM Invoices JOIN #VendorTotal
   ON Invoices.VendorID = #VendorTotal.VendorID and Invoices.InvoiceDate = #VendorTotal.FirstInvoiceDate
JOIN Vendors ON Vendors.VendorID = Invoices.VendorID
ASKER CERTIFIED SOLUTION
cyberkiwi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gamblsj

Thank you cyberkiwi. This problem was stumping my beginner's acumen.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.