• 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