Pedro Gomes-379488 (5/27/2014)

---

It's good to know about LAG and LEAD, that I didn't know of, but I use the approach below when faced with that kind of problem and don't have performance issues:

SELECT

T2.InvID, T2.Date, T2.NewLocationID AS LocationID, COUNT(*) AS ItemIdent INTO #Temp

FROM

Table AS T1

INNER JOIN Table AS T2 ON T1.InvID = T2.InvID AND T2.Date >= T1.Date

GROUP BY T2.InvID, T2.Date, T2.NewLocationID

SELECT T1.LocationID, DATEDIFF(d, T1.Date, T2.Date) AS QtyDaysInLocation

FROM #Temp AS T1 LEFT JOIN #Temp AS T2 ON T1.InvID = T2.InvID AND T1.ItemIdent = T2.ItemIdent - 1