Need a SQL2008 view that conditionally counts rows with reset

• dwood 63322

  SSC Enthusiast

  Points: 114

  More actions

  May 22, 2015 at 11:23 am

  #317624

  I have a table sorted by vendor, then item, then Status date with a QC Pass date and Fail date.

  I need a SQL 2008 view that counts how many consecutive times a Vendor/Item has passed QC.

  When it fails, the count resets to zero, then begins incrementing again.

  I need to know how to generate the last column (Count).

  I have tried using a ROW_Number() OVER(Partition BY, Order By...) command in the view,

  but I cannot seem to make it work right. Any ideas would be appreciated.

  VendorItemStatusDatePassDate Faildate Count

  10056322010-05-092010-05-091

  10056322012-12-152012-12-152

  10056322013-05-252013-05-253

  10056322014-11-172014-11-174

  10068942010-01-052010-01-051

  10068942011-05-062011-05-062

  10068942012-08-092012-08-093

  10068942013-06-192013-06-194

  10068942014-05-092014-05-090

  10068942014-09-182014-09-181

  10068942015-05-122015-05-122

  20025892010-05-092010-05-091

  20025892012-12-152012-12-152

  20025892013-05-252013-05-253

  20025892014-11-172014-11-170

  20098652010-01-052010-01-051

  20098652011-05-062011-05-060

  20098652012-08-092012-08-091

  20098652013-06-192013-06-192

  20098652014-05-092014-05-093

  20098652014-09-182014-09-184

  20098652015-05-122015-05-125

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 22, 2015 at 11:34 am

  #1800103

  Can you post your sample data as Insert statements along with DDL for the table? For an easy way to do it, check this: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

  You could also start looking at the following article: http://www.sqlservercentral.com/articles/T-SQL/68467/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• dwood 63322

  SSC Enthusiast

  Points: 114

  More actions

  May 22, 2015 at 11:51 am

  #1800110

  Here's the Insert. Thx for any assistance.

  CREATE TABLE mytable(

  Vendor INTEGER(3)

  , Item INTEGER(4)

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

• dwood 63322

  SSC Enthusiast

  Points: 114

  More actions

  May 22, 2015 at 11:53 am

  #1800111

  Here's the insert. Thx for any assistance.

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

• dwood 63322

  SSC Enthusiast

  Points: 114

  More actions

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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 22, 2015 at 12:07 pm

  #1800113

  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;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• dwood 63322

  SSC Enthusiast

  Points: 114

  More actions

  May 22, 2015 at 12:12 pm

  #1800117

  Thank you so much!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 22, 2015 at 12:25 pm

  #1800123

  I hope that you understand it before implementing it.

  If you have any questions, feel free to ask them.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2