Find dates when exactly records changed for user
-- Guessed table definition
CREATE TABLE dbo.Example
(
    [ID]            integer NOT NULL,
    [Name]          char(1) NOT NULL,
    EffectiveDate   datetime NOT NULL,
    [Status]        varchar(3) NOT NULL,

    CONSTRAINT CUQ_dbo_Example_Name_EffectiveDate
    UNIQUE CLUSTERED ([Name], EffectiveDate DESC),

    CONSTRAINT PK_dbo_Example_ID
    PRIMARY KEY NONCLUSTERED ([ID])
);


-- Sample data from the question
INSERT dbo.Example (ID, Name, EffectiveDate, [Status])
SELECT 1, 'A', CONVERT(datetime, '21-10-2014', 105), 'OFF' UNION ALL
SELECT 2, 'A', CONVERT(datetime, '20-10-2014', 105), 'OFF' UNION ALL
SELECT 3, 'A', CONVERT(datetime, '19-10-2014', 105), 'On' UNION ALL
SELECT 4, 'A', CONVERT(datetime, '18-10-2014', 105), 'On' UNION ALL
SELECT 5, 'A', CONVERT(datetime, '17-10-2014', 105), 'On' UNION ALL
SELECT 6, 'A', CONVERT(datetime, '16-10-2014', 105), 'OFF' UNION ALL
SELECT 7, 'A', CONVERT(datetime, '15-10-2014', 105), 'On' UNION ALL
SELECT 8, 'A', CONVERT(datetime, '14-10-2014', 105), 'On' UNION ALL
SELECT 9, 'A', CONVERT(datetime, '13-10-2014', 105), 'OFF' UNION ALL
SELECT 10, 'A', CONVERT(datetime, '12-10-2014', 105), 'OFF' UNION ALL
SELECT 11, 'A', CONVERT(datetime, '11-10-2014', 105), 'OFF';


Solution:

-- Query
SELECT
    E.[ID],
    E.[Name],
    E.EffectiveDate,
    E.[Status]
FROM dbo.Example AS E
WHERE
    NOT EXISTS
    (
        SELECT NULL
        FROM dbo.Example AS E2
        WHERE E2.[Name] = E.[Name]
        AND E2.[Status] = E.[Status]
        AND E2.EffectiveDate = DATEADD(DAY, -1, E.EffectiveDate)
    )
ORDER BY
    E.Name,
    E.EffectiveDate DESC;


Execution plan: