Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help getting a count by consecutive groups Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 7, 2014 9:12 AM
Points: 6, Visits: 12
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.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

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.
Post #1519281
Posted Tuesday, December 3, 2013 9:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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
Post #1519291
Posted Tuesday, December 3, 2013 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 7, 2014 9:12 AM
Points: 6, Visits: 12
Thank you for your fast reply! That is perfect, just what I needed.
Post #1519299
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse