Simplifying repeating elements of a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Simplifying repeating elements of a query?

Author

Topic

transistor
Starting Member

2 Posts

Posted - 2009-10-09 : 18:57:30

Hi there. I have this query working very nicely for me at the moment. I'll need to add more to it so I'm interested in keeping it readable for the next poor sod who has to work on it.

My question: The subquery in the WHEN statement is repeated in the THEN statement. Is there a way to avoid the repetition? e.g., In a scripting language one would assign the query to a variable so that


  If (ThisValue - PreviousValue) >= 0 Then
    output = ThisValue - PreviousValue
  Else
    output = ThisValue
  End If

could be simplified to


  x = ThisValue - PreviousValue
  If x >= 0 Then output = x Else output = ThisValue

which is a bit more readable and only does the calculation once.

Is it possible to do something similar in SQL? I've included some sample data if anyone wants to try it out.

if exists (select * from dbo.sysobjects where id = object_id(N'[MedianT]') 
		and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [MedianT]
GO
 
CREATE TABLE MedianT (_TIMESTAMP DATETIME, _VALUE int)
 
INSERT INTO MedianT (_TIMESTAMP, _VALUE)
SELECT '4/29/1962', 0 UNION
SELECT '5/1/1962', 5 UNION
SELECT '5/3/1962', 8 UNION
SELECT '5/4/1962', 11 UNION
SELECT '5/8/1962', 11 UNION
SELECT '5/10/1962', 14 UNION
SELECT '5/13/1962', 0 UNION
SELECT '5/14/1962', 4 UNION
SELECT '5/29/1962', 7 UNION
SELECT '5/29/1963', 7 UNION
SELECT '5/29/1967', 9 UNION
SELECT '5/29/1969', 3 UNION
SELECT '5/29/1974', 5 UNION
SELECT '5/29/1980', 8 UNION
SELECT '5/29/2000', 10
 
SELECT
	_TIMESTAMP,
	_VALUE,
	-- Now get the increase in count since the previous record.
	-- If this record value < prev record value then return this record value.
	Increase = CASE 
		WHEN 
			-- First occurrance of repeating query
			_VALUE - 
			(	SELECT _VALUE 
				FROM MedianT T 
				WHERE T._TIMESTAMP =
				(SELECT
					-- 
					MAX(_TIMESTAMP) FROM MedianT T2
					WHERE T2._TIMESTAMP < MedianT._TIMESTAMP
				)
			) >= 0 
		THEN
			-- First occurrance of repeating query
			 _VALUE - 
			(	SELECT _VALUE 
				FROM MedianT T 
				WHERE T._TIMESTAMP =
				(SELECT
					-- 
					MAX(_TIMESTAMP) FROM MedianT T2
					WHERE T2._TIMESTAMP < MedianT._TIMESTAMP
				)
			)
		ELSE
			_VALUE
	END
FROM MedianT

Many thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-09 : 22:09:07

The most efficient way to compare a row to a "previous" row is to do a self join by a sequencing column. Unfortunately in sql 2000 if you don't already have a natural sequence in the table there isn't a good way to do it without creating a another table with an identity column. But even that is far better than the repeating nested correlated sub-query you have.


create table #MedianT (rowid int identity(1,1), ts datetime, v int)

insert #MedianT (ts,v)
select _timestamp, _value
from   MedianT
order by _timestamp

select a.ts as [timestamp]
       ,a.v as [value]
       ,case when a.v - b.v > 0 then a.v - b.v else 0 end as increase
from #mediant a
left outer join #mediant b
       on b.rowid+1 = a.rowid

output:
timestamp               value       increase
----------------------- ----------- -----------
1962-04-29 00:00:00.000 0           0
1962-05-01 00:00:00.000 5           5
1962-05-03 00:00:00.000 8           3
1962-05-04 00:00:00.000 11          3
1962-05-08 00:00:00.000 11          0
1962-05-10 00:00:00.000 14          3
1962-05-13 00:00:00.000 0           0
1962-05-14 00:00:00.000 4           4
1962-05-29 00:00:00.000 7           3
1963-05-29 00:00:00.000 7           0
1967-05-29 00:00:00.000 9           2
1969-05-29 00:00:00.000 3           0
1974-05-29 00:00:00.000 5           2
1980-05-29 00:00:00.000 8           3
2000-05-29 00:00:00.000 10          2

Be One with the Optimizer
TG

transistor
Starting Member

2 Posts

Posted - 2009-10-10 : 03:40:10

That works and is much more readable!
You've also caused me to learn a little about temporary tables (and their disadvantages) and smart use of the id. (I have IDs on my table but there may be gaps when some data is weeded out in the SELECT so your solution solves that.)

Thank you TG for taking the time to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources