Number of days between status changes

Question

I've got the following solution, but I need to now update it to work a bit different. Here is my code that I've got now:

DECLARE @table TABLE (Status VARCHAR(50),  StartDate DATETIME)

INSERT INTO @table VALUES ('UP', '01/01/2012')
INSERT INTO @table VALUES ('UP', '04/15/2012')
INSERT INTO @table VALUES ('Down','06/12/2012')
INSERT INTO @table VALUES ('UP','10/04/2012')

SELECT
   t1.Status, t1.StartDate, ISNULL(t2.StartDate,GetDate()) AS 'EndDate',
   DateDiff(day,t1.startdate,ISNULL(t2.StartDate,GetDate())) as StatusDays
FROM
   (SELECT Status, StartDate, ROW_NUMBER() OVER 
    (ORDER BY StartDate) AS 'num' FROM @table) t1
LEFT JOIN
   (SELECT Status, StartDate, ROW_NUMBER() OVER 
    (ORDER BY StartDate) AS 'num' FROM @table) t2
    ON t1.num = t2.num-1

This gets me results, but the first two rows, are both UP. Here is what I get now.

Status  StartDate   EndDate     StatusDays
UP          2012-01-01      2012-04-15      105
UP          2012-04-15      2012-06-12       58
Down        2012-06-12      2012-10-04      114
UP          2012-10-04      2013-01-29      117

So, I would like to see this summary:

Status  StartDate   EndDate     StatusDays
UP          2012-01-01      2012-06-12      163
Down        2012-06-12      2012-10-04      114
UP          2012-10-04      2013-01-29      117

in your second example you are adding the two up rows together. But your enddate doesnt reflect this. Should it be enddate 2012-06-12? To me it looks like from 2012-01-01 to 2012-04-15 you have had 163 upvotes where infact you had 105. — gh9, Jan 29 '13 at 22:19
@gh9 If I am understanding it correctly, the status is the same and the start/end date match between the first two rows so they should be totaled together — Taryn, Jan 29 '13 at 22:21
I think the summary he would like to see needs to have its enddate updated for the the status of UP with a startdate at 2012-01-01. — gh9, Jan 29 '13 at 22:23
Should the join perhaps be `ON T2.num = T1.num - 1`? Assuming `T2` is supposed to be the preceding record. You may want to consider using more explicit aliases, like `T` and `TPrev` (or `TNext`, if I've read you wrong). — , Jan 29 '13 at 22:26

score 3 · Accepted Answer · answered Jan 29 '13 at 22:58

The following T-SQL isn't the most elegant but it will do the job:

with mycte as 
(
    select t.Status, t.StartDate, ROW_NUMBER() over (order by t.startdate) as rn
    from @table as t
), 
mycte2 as
(
    select m.Status, m.StartDate, ROW_NUMBER() over (order by m.rn) as rn2
    from mycte as m
    where not exists
    (
        select 1
        from mycte as m2
        where m2.rn=m.rn-1 and m2.status=m.Status
    )
)

select m2.Status, m2.StartDate, cast(isnull(m3.StartDate, getdate()) as date) as EndDate, 
    datediff(day,m2.StartDate, cast(isnull(m3.StartDate, getdate()) as date)) as StatusDays
from mycte2 as m2
left outer join mycte2 as m3
    on m3.rn2=m2.rn2+1

Nice Paul. I need this for some temporary look up stuff. Looking for quick functionality, so it doesn't have to be pretty! — Jeff Reddy, Jan 29 '13 at 23:06

Number of days between status changes

1 Answers1