Simply the SQL query script by putting 2 seperate sql query into 1 sql query somehow?

Dev Shed Forums Sponsor:

July 10th, 2008, 01:17 PM

fletchsod

I lov C in AIX/Linux, hate C++

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jul 2003

Location: Jacksonville, Florida

Posts: 1,521

Time spent in forums: 1 Week 2 Days 19 h 45 m 9 sec
Reputation Power: 8

Simply the SQL query script by putting 2 seperate sql query into 1 sql query somehow?

Anyone know how to combine these 2 sql queries into 1? Noticed it use #tmpResult which I'm trying to remove. By taking out the #tmpResult, I noticed it's kind of tought to do so by fitting the "NumDays" field into everything. So, I welcome some help.

Code:

SELECT TOP 5
	SoldID, 
	Year, 
	MakeID, 
	ModelID, 
	SalePrice, 
	DATEDIFF(Day, StockDate, SoldDate) AS NumDays
INTO 	#tmpResult 
FROM 		tblSold
WHERE SaleType = 'R'

Code:

SELECT	TOP 5 COUNT(SoldID) AS [Count], 
	[Year], 
	MakeID, 
	ModelID, 
	AVG(SalePrice),
	AVG(NumDays)
FROM  #tmpResult
GROUP BY   [Year], 
		MakeID, 
		ModelID
ORDER BY AVG(NumDays) ASC

Thanks...

July 11th, 2008, 03:33 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,826

Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 29 m 39 sec
Reputation Power: 278

Apart from the usage of reserved words (count, year), what's wrong with this?

sql Code:

Original - sql Code
SELECT TOP 5 COUNT(SoldID) AS [Count], [Year], MakeID, ModelID, AVG(ts.SalePrice), AVG(DATEDIFF(Day, StockDate, SoldDate)) FROM tblSold GROUP BY [Year], MakeID, ModelID ORDER BY AVG(DATEDIFF(Day, StockDate, SoldDate)) ASC

SELECT  
  TOP 5 
 COUNT(SoldID) AS [COUNT], 
 [Year], 
 MakeID, 
 ModelID, 
 AVG(ts.SalePrice),
 AVG(DATEDIFF(Day, StockDate, SoldDate))
FROM  
 tblSold
GROUP BY   
 [Year], 
 MakeID, 
 ModelID
ORDER BY 
 AVG(DATEDIFF(Day, StockDate, SoldDate)) ASC

Or rather, would you care to explain the top 5 in both queries?

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

July 11th, 2008, 08:30 AM

fletchsod

I lov C in AIX/Linux, hate C++

Join Date: Jul 2003

Location: Jacksonville, Florida

Posts: 1,521

Time spent in forums: 1 Week 2 Days 19 h 45 m 9 sec
Reputation Power: 8

Yea, that would work... I'm surprised the DATEDIFF() works now in the "ORDER BY" clause. It didn't yesterday, so I must had made an error somewhere and MSSQL gave me the wrong error message.

The original programmer did this...

Code:

/*****************************************/
CREATE TABLE #tmpGroup
(
	[Count] INT,
	[Year] INT,
	MakeID VARCHAR(50),
	ModelID VARCHAR(50),
	[SalePrice] Decimal(19,2),
	[Avg Days in Inventory] INT
)
/*****************************************/

SELECT * INTO 	#tmpResult 
FROM 		vwSoldPriceNational

INSERT INTO #tmpGroup	
(
            [Count],
	[Year],
	MakeID,
	ModelID,
	[SalePrice],
	[Avg Days in Inventory])
SELECT	TOP 5 
             COUNT(SoldID) AS [Count], 
	[Year], 
	MakeID, 
	ModelID, 
	AVG(SalePrice),
	AVG(NumDays)
FROM	#tmpResult
GROUP BY  [Year], 
	MakeID, 
	ModelID
ORDER BY AVG(NumDays) DESC

SELECT [Year],
	MakeID AS 'Make',
	ModelID AS 'Model',
	SalePrice AS 'Sale Price',
	[Avg Days in Inventory]
FROM #tmpGroup

So, I was revising and revising to get down to one sql query. So the best one I got was in the original posting. I had to cuz it takes too long to get the result.

Thanks for the posting. Sometime having 2 heads are better than one if I had trouble pin-pointing or narrowing down the script. I got to cut the response time from 14 seconds to 3 seconds with the new sql query.

Thanks...

July 11th, 2008, 09:02 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,826

Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 29 m 39 sec
Reputation Power: 278

The original programmer should be beaten, and hard ...

Comments on this post

fletchsod agrees!

July 11th, 2008, 09:09 AM

fletchsod

I lov C in AIX/Linux, hate C++

Join Date: Jul 2003

Location: Jacksonville, Florida

Posts: 1,521

Time spent in forums: 1 Week 2 Days 19 h 45 m 9 sec
Reputation Power: 8

Yea! You should have seen how many views, stored-procedures and unnecessary physical tables the original programmer did. It's a nightmare and it's been giving me headache, especially with so many jumping around in the script. LOL! You should have see the way he talks and the ways he makes everyone feel small. I'm still working on cleaning up and removing waste stuffs for almost 3 years now. The problem with him is he doesn't think and he does things the easy way.

Last edited by fletchsod : July 11th, 2008 at 09:17 AM.

July 11th, 2008, 09:20 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,826

Time spent in forums: 2 Months 3 Weeks 1 Day 10 h 29 m 39 sec
Reputation Power: 278

I hope he is a thing of the past.

July 11th, 2008, 11:21 AM

fletchsod

I lov C in AIX/Linux, hate C++

Join Date: Jul 2003

Location: Jacksonville, Florida

Posts: 1,521

Time spent in forums: 1 Week 2 Days 19 h 45 m 9 sec
Reputation Power: 8

Quote:

Originally Posted by pabloj

I hope he is a thing of the past.

Yep! He quit 2 years ago, although not sure why he quit though.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: