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.

Martin

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;

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 
(
	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;