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.