I'm sorry, something is going on with the forum which won't publish your posts immediately. I guess that it has to do with the spam filter and your posts might be visible eventually (I can see them in the reply page).

Here's a solution using a gaps and islands technique which has a good performance, although it's not the best.

CREATE TABLE mytable(

Vendor INT

, Item INT

, StatusDate DATE

, PassDate DATE

, Faildate DATE

);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,5632,'5/9/2010','5/9/2010',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,5632,'12/15/2012','12/15/2012',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,5632,'5/25/2013','5/25/2013',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,5632,'11/17/2014','11/17/2014',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,6894,'1/5/2010','1/5/2010',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,6894,'5/6/2011','5/6/2011',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,6894,'8/9/2012','8/9/2012',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,6894,'6/19/2013','6/19/2013',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,6894,'5/9/2014',NULL,'5/9/2014');

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,6894,'9/18/2014','9/18/2014',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (100,6894,'5/12/2015','5/12/2015',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,2589,'5/9/2010','5/9/2010',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,2589,'12/15/2012','12/15/2012',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,2589,'5/25/2013','5/25/2013',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,2589,'11/17/2014',NULL,'11/17/2014');

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,9865,'1/5/2010','1/5/2010',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,9865,'5/6/2011',NULL,'5/6/2011');

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,9865,'8/9/2012','8/9/2012',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,9865,'6/19/2013','6/19/2013',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,9865,'5/9/2014','5/9/2014',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,9865,'9/18/2014','9/18/2014',NULL);

INSERT INTO mytable(Vendor,Item,StatusDate,PassDate,Faildate) VALUES (200,9865,'5/12/2015','5/12/2015',NULL);

WITH CTE AS(

SELECT *,

ROW_NUMBER() OVER(PARTITION BY Vendor, Item ORDER BY StatusDate) -

ROW_NUMBER() OVER(PARTITION BY Vendor, Item, FailDate ORDER BY StatusDate) AS grouper

FROM mytable

)

SELECT Vendor,

Item,

StatusDate,

PassDate,

Faildate,

CASE WHEN PassDate IS NULL THEN 0

ELSE ROW_NUMBER() OVER(PARTITION BY Vendor, Item, Grouper, FailDate ORDER BY StatusDate) END AS Count

FROM CTE

ORDER BY Vendor, Item, StatusDate;

GO

DROP TABLE mytable;