Find dates when exactly records changed for user I need to find the dates when exactly the status really changed.

Order should be analysed from Start to end period:

Start Period : 11-10-2014
End Period : 21-10-2014


Data:

ID, Name, Effective_date, Status
1   A       21-10-2014      OFF
2   A       20-10-2014      OFF
3   A       19-10-2014      On
4   A       18-10-2014      On
5   A       17-10-2014      On
6   A       16-10-2014      OFF
7   A       15-10-2014      On
8   A       14-10-2014      On
9   A       13-10-2014      OFF
10  A       12-10-2014      OFF
11  A       11-10-2014      OFF


I am using SQL Server 2000.

Expected output:

ID, Name, Effective_date, Status

2   A       20-10-2014      OFF
5   A       17-10-2014      On
6   A       16-10-2014      OFF
8   A       14-10-2014      On
11  A       11-10-2014      OFF
-- 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: