Wanted: smarter solution for this query

Transact-SQL
1

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.

Martin

2

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
(
	select 
		Resource,
		Lead(Resource, 1) over (order by OperatorNr) NextResource,
		time + Lead(Time,1) over (order by OperatorNr) as TotalTime
	from
		table

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

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?

4

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 
(
	select
		*,
		row_number() over (order by OperatorNr)-Resource as grpId
	from
		#tmp
),
cte2 as
(
	select 
		*,
		min(resource) over (partition by grpid) as ResourceStart,
		count(grpid) over(partition by grpid)  as ConsecutiveCount
	from cte
)
select
	grpid,
	resourceStart,
	SUM(Time) as TotalTime
from
	cte2
where
	ResourceStart = 12
	and ConsecutiveCount = 2
group by
	grpid,
	resourceStart;