SQL Server – How to Limit LAG Function to a Subset

My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.

This solution assumes there is only a single Value for each RecordDate. Performance will be better with tuned indexes, and probably breaking up the process using a temporary table or table variable. I used your script for test data. The output may not be exactly how you want it, but the process to get you close is what's important -- it works by filtering the rows to only the rows that start an interval, and then fabricates the end date by offsetting the results from the previous step.

WITH a AS
(
    SELECT
        Value,
        RecordDate,
        ROW_NUMBER() OVER(ORDER BY RecordDate) AS RN
        FROM [dbo].[RateTable] rt
),
b AS
(
    SELECT
        a1.Value,
        a1.RecordDate,
        ROW_NUMBER() OVER(ORDER BY a1.RecordDate) AS RN
        FROM a a1
        LEFT OUTER JOIN a a2 ON a2.RN = a1.RN - 1
        WHERE
            (a1.Value != a2.Value) OR
            (a2.RN IS NULL)
)
SELECT
    b1.Value,
    b1.RecordDate AS StartDate,
    b2.RecordDate AS EndDate,
    DATEDIFF(DAY, b1.RecordDate, b2.RecordDate) + 1 AS Duration /* Fixme? */
    FROM b b1
    LEFT OUTER JOIN b b2 ON b2.RN = b1.RN + 1
    ORDER BY b1.RecordDate;

Output:

Value   StartDate                EndDate                  Duration
1       2012-01-01 00:00:00.000  2012-01-04 00:00:00.000  4
5       2012-01-04 00:00:00.000  2012-01-06 00:00:00.000  3
12      2012-01-06 00:00:00.000  2012-01-07 00:00:00.000  2
1       2012-01-07 00:00:00.000  NULL                     NULL