Help getting a count by consecutive groups

• aadkins 91421

  Valued Member

  Points: 56

  More actions

  December 3, 2013 at 9:21 am

  #279575

  I am hoping someone can help me figure out a solution to a TSQL problem I have. I have data that in a simplified form looks like this.

  Status Create Next Create

  Busy 2013-09-05 14:42:46.8272013-09-09 08:40:18.487

  Busy 2013-09-09 08:40:18.4872013-09-09 11:33:27.927

  At Lunch 2013-09-09 11:33:27.9272013-09-09 13:02:27.133

  Busy 2013-09-09 13:02:27.1332013-09-10 14:37:58.107

  Busy 2013-09-10 14:37:58.1072013-09-13 08:41:04.030

  What I need to find out is how many of each status do I have consecutively.

  Business rules: If a status exists then changes to another status then it is counted as a separate group. So for the example above it would be three groups: busy(2), at lunch(1), busy(2).

  Any ideas? I have looked at some examples of gaps and islands but I am not sure how to use it to get the count I need here.

• Keith Tate

  SSCrazy Eights

  Points: 9938

  More actions

  December 3, 2013 at 9:33 am

  #1670442

  This query should get you what you need:

  create table CreateStatus (CreateStatus varchar(256), LastCreate datetime, NextCreate datetime);

  go

  insert CreateStatus

  values ('Busy','2013-09-05 14:42:46.827','2013-09-09 08:40:18.487')

  ,('Busy','2013-09-09 08:40:18.487','2013-09-09 11:33:27.927')

  ,('At Lunch','2013-09-09 11:33:27.927','2013-09-09 13:02:27.133')

  ,('Busy','2013-09-09 13:02:27.133','2013-09-10 14:37:58.107')

  ,('Busy','2013-09-10 14:37:58.107','2013-09-13 08:41:04.030')

  ,('At Lunch','2013-09-11 11:33:27.927','2013-09-14 13:02:27.133')

  ;

  with CTE as

  (

  select

  CreateStatus

  ,LastCreate

  ,NextCreate

  ,ROW_NUMBER() over (order by CreateStatus, LastCreate) -ROW_NUMBER() over (order by LastCreate) grp

  from CreateStatus

  )

  SELECT

  CreateStatus

  ,count(*) as StatusCount

  FROM CTE

  group by

  grp

  ,CreateStatus

  ---

  
  Microsoft Certified Master - SQL Server 2008
  Follow me on twitter: @keith_tate

  Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• aadkins 91421

  Valued Member

  Points: 56

  More actions

  December 3, 2013 at 9:44 am

  #1670448

  Thank you for your fast reply! That is perfect, just what I needed.