Gaps And Islands: Call Center Edition

Kevin Feasel



Kathy Kellenberger solves an issue with tracking contiguous calls within a call center:

To come up with a solution, I first thought about how to solve it with a traditional cursor. I won’t provide a cursor solution here, but it would involve looping through the rows in order and finding the first and last employee rows. When the last EmpID doesn’t match the next EmpID, then the previous row is the last call for the previous EmpID and the current row is the first call for the next EmpID.

NOTE: The solution to this problem assumes that there are at least two calls per shift.

In 2012, four T-SQL window functions became available that let you look at expressions from different rows: LAG, LEAD, FIRST_VALUE, and LAST_VALUE. For this puzzle, LAG and LEAD seemed to be the answer.  In this case, I used the optional default parameter to replace any NULLs with -1.

This is a particular solution for the Gaps and Islands problem; here’s Itzik Ben-Gan on the topic.

Related Posts

The Value Of Schemabinding

Vitaly Bruk explains what schemabinding is and why we sometimes need WITH SCHEMABINDING in our code: In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables […]

Read More


Rob Farley looks at how GROUP BY and DISTINCT and lead you down different execution plan paths: What I want to explore in this post is the particular example that we both used… to bring an important point that could be missed because of the similarity of our examples. You see, we both happened to […]

Read More


February 2017
« Jan Mar »