SQLTeam.com | Weblogs | Forums

Wanted: smarter solution for this query


I have this table that describes the times different resources need during a production cycle of one article.

 OperationNr  Ressource      Time 
     10           12           5
     20           13          10
     30           42          10
     40           13          15

I need to calculate the total time for a series of resources (e.g. 12, 13 - specified by the user) that are scheduled in a continuous order.
In this example the result would be 15. The last row of the table is not counted because row#3 uses a resource that is not in the list (12,13). Only the first n rows with a resource in (12,13) have to be summed up.

I came up with this, but I have this feeling that it could be done better, faster, smarter:

Select SUM(time)
From table
Where OperationNr < (Select TOP 1 OperationNr
                     From table
                     Where Resource not in (12,13)
                     Order by OperationNr)

I have to include this query in a massive query that already has some performance issues, therefore execution time matters.



If you are on SQL 2012 or later, you can use the LEAD (or LAG) function - which might be more efficient because it would touch the table only once.

;with cte as
		Lead(Resource, 1) over (order by OperatorNr) NextResource,
		time + Lead(Time,1) over (order by OperatorNr) as TotalTime

select TotalTime from cte where Resource = 12 and NextResource = 13;


Thank you James and yes, I'm using SQL 2012. The resource list (12, 13) was just an example, but it could be even 3 or more items in the list. It's a parameter set through a user interface. How would that change the syntax of your solution?


Look for groups and islands problem in SQL. There are several solutions, in particular some of the code and descriptions posted by Ben-Gan. The following code demonstrates approximately how you would use the groups and islands pattern to write this query.

create table #tmp(OperatorNr int, Resource int, Time int);
insert into #tmp values (10,12,5),(20,13,10),(30,42,10),(40,13,15);

;with cte as 
		row_number() over (order by OperatorNr)-Resource as grpId
cte2 as
		min(resource) over (partition by grpid) as ResourceStart,
		count(grpid) over(partition by grpid)  as ConsecutiveCount
	from cte
	SUM(Time) as TotalTime
	ResourceStart = 12
	and ConsecutiveCount = 2
group by