How to improve this query (repeating sql) ? - 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)

How to improve this query (repeating sql) ?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2010-01-07 : 09:30:09

I have a query that gets the sum of a field. I need to do 2 checks on it and am having to repeat the original query. I'm assuming the result is cached so it doesn't actually run 3 times but how can I tidy it up? I'd be inclined to use a varible but its tied to row we are in.

What am I missing?

SELECT UniqueId, (SELECT SUM(Capacity)
						FROM Seating_Group
						WHERE AreaId = Seating_Area.UniqueID AND UniqueId Not In
						(
							SELECT * FROM dbo.GetOccupiedGroups(0,@StartDate,@EndDate)
						)
					) as Capacity
	FROM Seating_Area
	WHERE (SELECT SUM(Capacity)
		FROM Seating_Group
		WHERE AreaId = Seating_Area.UniqueID AND UniqueId Not In
		(
			SELECT * FROM dbo.GetOccupiedGroups(0,@StartDate,@EndDate)
		)
	) is not null 
	AND (SELECT SUM(Capacity)
		FROM Seating_Group
		WHERE AreaId = Seating_Area.UniqueID AND UniqueId Not In
		(
			SELECT * FROM dbo.GetOccupiedGroups(0,@StartDate,@EndDate)
		)
	) >= @RequiredSeats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 09:39:07

make a udf:-


CREATE FUNCTION testudf
(
@StartDate datetime,
@EndDate datetime
)
RETURNS datatype
AS
BEGIN
DECLARE @Ret int
SELECT @Ret=SUM(Capacity)
						FROM Seating_Group
						WHERE AreaId = Seating_Area.UniqueID AND UniqueId Not In
						(
							SELECT * FROM dbo.GetOccupiedGroups(0,@StartDate,@EndDate)
						)
END


SELECT UniqueId, dbo.testudf(@StartDate,@EndDate) as Capacity
	FROM Seating_Area
	WHERE dbo.testudf(@StartDate,@EndDate) is not null 
	AND dbo.testudf(@StartDate,@EndDate)>= @RequiredSeats

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2010-01-07 : 10:34:03

Have done that elsewhere, just seems strange there's no way to do it in-line.

Thanks visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:37:43

quote:
Originally posted by FruitBatInShades

Have done that elsewhere, just seems strange there's no way to do it in-line.

Thanks visakh.

do it inline you've repeat everything like you did.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources