Help on script SQL server 2008

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help on script SQL server 2008

Author

Topic

sweetpepper66
Starting Member

10 Posts

Posted - 2011-11-17 : 13:18:06

Hello,

I'm having hard time to write that script and I would like to know if somebody can give me a hint or help on the question below, I would really appreciate..... I have done the other scripts asked but this one is driving insane....
– The following script uses 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 temporary table in place of the derived table. Make sure your script tests for the existence of any objects it creates.
Here is what I have so far
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

Any help would be welcomed.....Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 13:45:40

When's the assignment due?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 13:52:39

But you don't even need a derived table...did teach trell you to do it this way???

A very poor choice as an example...you will now go off into the world thinking that's the way it should be done


  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

Does he WANT you to use a temp Table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 13:53:31

And what school is and what teacher...they probably have a blog

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sweetpepper66
Starting Member

10 Posts

Posted - 2011-11-17 : 13:55:56

First Thank for your feedback, I really appreciate it and yes he does want that.....This is my last question of my assignment and it is due tomorrow by midnight.
I actually didn't quite understand the question. It didn't make sense to me.

Thank you again

sweetpepper66
Starting Member

10 Posts

Posted - 2011-11-17 : 13:58:45

Well the teacher is a Java, C++ and cobol instructor which is not related to SQL for some scripts and doesn't really give help!!!!! Got to deal with anyway.....So doing the best I can.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 14:13:32

quote:
Originally posted by sweetpepper66

Well the teacher is a Java, C++ and cobol instructor which is not related to SQL for some scripts and doesn't really give help!!!!! Got to deal with anyway.....So doing the best I can.

Well HE SHOULD..bobo

"Hi..I teach IT Courses..but I majored in American Lit"

So he really wants you to use a temp table

Is this SQL Server Express or some other database platform?

EDIT: And Oh, the request about the existence of the table..it only lives with in the lif of the thread...so bozo must be reading something in a book and doesn't understand

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sweetpepper66
Starting Member

10 Posts

Posted - 2011-11-17 : 14:20:12

Well I'm an IT going to the Bachelor degree and had to take this class, they didn't give me a choice actually. Yes he wants me to use temp table. I was wondering if I have to use the code test table first.

sweetpepper66
Starting Member

10 Posts

Posted - 2011-11-17 : 14:21:39

Oops sorry, I'm using SQL server 2008 Management studio, the basic I guess.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 14:26:12

no that's correct ok, good

So what does he want in the temp table? The min dates?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-17 : 14:37:42

The example may be contrived, but surely it does no harm to consider using Derived or Pre-created Temp (or even permanent) tables?

But I'm assuming that after the assignment there would then be some debate in class about the relative merits of the various methods.

Add to that debate that maybe Oracle, or A.N.Other database, would actually perform better with ONE where SQL Server would perform better with THE OTHER.

Plenty of material for discussion I reckon

"the teacher is a Java, C++ and cobol instructor"

But there again you may be right Brett!

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 14:43:48

Now I'm curious. Now, with temp tables, you can have the exact same table name in different threads..but HOW does it know to return you the correct id from sysobjects for the thread you are in.

If you want to see, create a temp table in 2 SSMS Windows..then run this in both

SELECT * FROM tempdb..sysobjects
WHERE id IN (SELECT OBJECT_ID('tempdb..#myTable99'))

You will see the 2 different Id's in each window

Anyway, hope this helps, but NO ONE would do this because the script you wrie would instantiate a new temp table for every script. I do the drop at the end only because it's a good practice to clean up. SQL Server will clean up after the thread is done...but ya never know.


IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id IN (SELECT OBJECT_ID('tempdb..#myTable99')))
	DROP TABLE #myTable99
GO

CREATE TABLE #myTable99(VendorID int, MIN_InvoiceDate date)
GO

INSERT INTO #myTable99(VendorID, MIN_InvoiceDate)
     SELECT VendorID, InvoiceDate
       FROM Invoices
   GROUP BY VendorID
   
     SELECT t1.VendorName, t1.MIN_InvoiceDate, v.InvoiceTotal
       FROM #myTable99 t1
 INNER JOIN Vendors v
		 ON t1.VendorID = v.VendorID
   ORDER BY t1.VendorName, t1.MIN_InvoiceDate
GO

DROP TABLE #myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 14:45:37

quote:
Originally posted by Kristen
But I'm assuming that after the assignment there would then be some debate in class about the relative merits of the various methods.

Yeah right..

quote:

Plenty of material for discussion I reckon

"the teacher is a Java, C++ and cobol instructor"

But there again you may be right Brett!

And what's different?

"You may be wrong, but you may be right...." -- BJ

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sweetpepper66
Starting Member

10 Posts

Posted - 2011-11-17 : 15:04:48

A big Thanks to both of you.... I am going to work on it...
And no, there would not be any discussion in class about that, all he does it's reading the pps and that's it!!! You're on your own. But anyway, again thank you very much for your help.

:)

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 15:11:55

So if you're going into IT...what do you want to do?

Data Architect, Data Modeler, DBA??? (hint, Hint)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sweetpepper66
Starting Member

10 Posts

Posted - 2011-11-17 : 15:59:12

I would not go to database, hell NOWAY..... to be honest I do not like it. Being already a network administrator, I decided to go to IT Management. I think it would be interesting....But the downside of it, I have to take some "damn" classes... For some it refresh my memory lol which is not too bad.....:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-17 : 18:43:34

Make it a good habit to prefix your column names with the tables alias.
It makes code much easier to debug.

;WITH cteSource(VendorName, FirstInvoiceDate, InvoiceTotal, SeqID)
AS (
	SELECT	VendorName,
		InvoiceDate AS FirstInvoiceDate,
		InvoiceTotal,
		ROW_NUMBER() OVER (PARTITION BY VendorID ORDER BY InvoiceDate) AS SeqID
	FROM	dbo.Invoices
)
SELECT		v.VendorName,
		s.FirstInvoiceDate,
		s.InvoiceTotal
FROM		cteSource AS s
INNER JOIN	dbo.Vendors AS v ON v.VendorID = s.VendorID
WHERE		SeqID = 1
ORDER BY	v.VendorName,
		s.FirstInvoiceDate

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources