CODEWITHSUNDEEP
sql-server
jyim89
jyim89

Reputation: 245

sql server - query. Returning time between failures

I have details on what builds passed and what builds failed. I want to be able to measure the time in between failures (meaning there is at least 1 pass in between 2 failures). I have example data and example queries I have so far linked here.

What I want my query to output given this data is just 2 rows

FailedDt             |        SecondFailedDt
-------------------------------------------------

1/2/2010 12:00:00 AM |        1/4/2010 12:00:00 AM

1/6/2010 12:00:00 AM |        1/10/2010 12:00:00 AM

How can I modify my query to get the output I want?

Upvotes: 1

Views: 53

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

This might be much easier than your approach:

Just paste this into an empty query window and execute... Adapt to your needs

declare @rsBuildDetails table(dt datetime, build varchar(255), val varchar(255));

insert into @rsBuildDetails (dt, build, val) values 
 ('20100101', '1', 'pass')
,('20100102', '2', 'fail')
,('20100103', '3', 'pass')
,('20100104', '4', 'fail')
,('20100105', '5', 'fail')
,('20100106', '6', 'fail')
,('20100107', '7', 'pass')
,('20100108', '8', 'pass')
,('20100109', '9', 'pass')
,('20100110', '10', 'fail');

with passed as
(
    select * 
    from @rsBuildDetails
    where val='pass'
)
select distinct preFail.dt AS FailedDt
               ,postFail.dt AS SecondFailedDt
from passed
outer apply(
    select top 1 pre.*
    from @rsBuildDetails as pre
    where pre.dt<passed.dt 
      and pre.val='fail'
    order by pre.dt desc
) as preFail
outer apply(
    select top 1 post.*
    from @rsBuildDetails as post
    where post.dt>passed.dt 
      and post.val='fail'
    order by post.dt asc
) as postFail
where preFail.dt IS NOT NULL and postFail.dt IS NOT NULL

The result

FailedDt                SecondFailedDt
2010-01-02 00:00:00.000 2010-01-04 00:00:00.000
2010-01-06 00:00:00.000 2010-01-10 00:00:00.000

Upvotes: 1

Related Questions