1

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
| improve this question | | | | |
  • 2
    ...checking calendar... solutions for this type of problem are much easier to write, and perform much better, on SQL Server 2005 and above. Is there a reason you're clinging to this 15-year old version that has been out of any type of support for years? – Aaron Bertrand Dec 9 '14 at 20:28
  • There is a reason and thats why I mentioned the version. I can easily resolve it on version above than 2005 using pivot. – dev777 Dec 10 '14 at 3:12
  • Can there be gaps in the dates? Or it is guaranteed that there is one row for every consecutive date? – ypercubeᵀᴹ Dec 10 '14 at 10:49
3
-- 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:

Execution plan

| improve this answer | | | | |
1
select t.*
from tableName t 
join tableName tt
    on (tt.ID = t.ID+1 and tt.[Status] <> t.[Status]) 
union
select * from (select top 1 * from tableName order by Effective_date)T

it's not pretty, but works

| improve this answer | | | | |
  • 3
    You should add that it works only if ID values have no gaps. – ypercubeᵀᴹ Dec 10 '14 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.