December 3, 2013 at 9:21 am
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.
December 3, 2013 at 9:33 am
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
December 3, 2013 at 9:44 am
Thank you for your fast reply! That is perfect, just what I needed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply