Home Forums SQL Server 2008 SQL Server 2008 - General Need a SQL2008 view that conditionally counts rows with reset RE: Need a SQL2008 view that conditionally counts rows with reset

  • May 22, 2015 at 11:56 am

    #1800112

    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);