Query optimization - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query optimization

Author

Topic

Mountain_Nerd
Starting Member

28 Posts

Posted - 2005-06-27 : 12:23:33

I am interested in reporting value change in records from year to year (e.g. let me know if the value went down from last year). I written a query to do this -- and it works. BUT, knowing that I am no SQL guru, I was wondering if any of you could provide me with a BETTER or more "standard" way of approaching this problem.

Please advise. Thanks.

CREATE TABLE #ValueChange (
pID INT, 
pYear INT,
pValue INT)
GO

INSERT INTO #ValueChange
	SELECT
	1,2001,70443 UNION ALL SELECT
	1,2002,75474 UNION ALL SELECT
	1,2003,78246 UNION ALL SELECT
	1,2004,77271 UNION ALL SELECT
	2,2001,70500 UNION ALL SELECT
	2,2002,71000 UNION ALL SELECT
	2,2003,72000 UNION ALL SELECT
	2,2004,73000 
	
DECLARE @year INT
DECLARE @value INT
SET @year = 2004

SELECT 
	cy.pID, 
	cy.pValue AS CurrentValue,
	ly.pValue AS PreviousValue,
	(CAST((cy.pValue-ly.pValue) AS DECIMAL(9,0))/cy.pValue)*100 AS Change
FROM
	(SELECT pID, pYear, pValue
	FROM #ValueChange
	WHERE pYear = @year
	AND pValue is not NULL
	AND pValue <> 0)cy
INNER JOIN
	(SELECT pID, pYear, pValue
	FROM #ValueChange
	WHERE pYear = @year - 1
	AND pValue is not NULL
	AND pValue <> 0)ly
ON
	cy.pID = ly.pID
WHERE
	(cy.pValue-ly.pValue) < 0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-27 : 13:43:30

I would just simplify slightly (which also allows for mutliple rows returned):


select	py.pYear PrevPYear
	,y.pYear PYear
	,isNull(y.pID,py.pID) pID
	,Value = y.pValue
	,NextValue = py.pValue
	,Change = case when y.pValue is null then 0 else (CAST((y.pValue-py.pValue) AS DECIMAL(9,0))/y.pValue)*100 end
from	#ValueChange y
full join #ValueChange py
		on y.pID = py.pID
		and y.pyear = py.pyear+1
/*
where	y.pYear = 2004
and	y.pValue < py.pValue
*/
order by 3,1

Be One with the Optimizer
TG

Mountain_Nerd
Starting Member

28 Posts

Posted - 2005-06-27 : 16:20:07

Thanks. I knew there would be an efficient way to do it with a "full self join" -- I just didn't see it. These are the sort of tricks that I need to be more adept at implementing. Again, thanks.

--MN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources