Query Problem - Counting Blocks

Question

Post reply

Query Problem - Counting Blocks

julian.rudall

Old Hand

Points: 301
More actions
February 11, 2009 at 5:13 am

#130808

Hello,
I have table that contains Emp Id, Date, Code.
The code identifies if the employee is absent or present.
I have written a query that will identify if there is an absent code in the "Code" field, so that my output looks like this
Emp Date Id
1234 02/02/2009 0
1234 03/02/2009 1
1234 04/02/2009 1
1234 05/02/2009 0
1234 06/02/2009 1
1234 07/02/2009 1
1234 08/02/2009 0
In the above 0 = not absent and 1 = absent. What I need to do is count the blocks of "1's" - so in the above my result should be 2 as there are 2 blocks of 1's seperated by 0's.
Can anyone help please?
Many Thanks
Julian

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

This should give you all of the blocks

with cte as (select Emp_Id , Date, Code,

row_number() over(partition by Emp_Id order by Date)-

row_number() over(partition by Emp_Id,Code order by Date) as rndiff

from mytable)

select Emp_Id,Code,min(Date) as BlockStart,max(Date) as BlockEnd,

count(*) as NumberInBlock

from cte

group by Emp_Id,Code,rndiff

--having count(*)>1

order by min(Date)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

julian.rudall Old Hand Points: 301 More actions · Answer 2

Hiya Mark

Thanks, this works a real treat on my 2005 database.

However, a database for another entity within the Company is a SQL Server 2000 database - I only just found that out when I attempted to run the code that you very kindly wrote for me.

Any suggestions??? 🙂

Many Thanks

Julian.

null-512060 SSChasing Mays Points: 613 More actions · Answer 3

Utilizing code posted by Mark:

SELECT Emp, Id, MIN(Date) BlockStart, MAX(Date) BlockEnd, COUNT(*) AS NumberInBlock

FROM (SELECT Emp, Date, Id

,(SELECT COUNT(1) FROM dbo.Test t2

WHERE t2.Date <= t1.Date

AND t2.Emp = t1.Emp) -

(SELECT COUNT(1) FROM dbo.Test t2

WHERE t2.Date <= t1.Date

AND t2.Id = t1.Id

AND t2.Emp = t1.Emp) rank

FROM dbo.Test t1) t3

GROUP BY Emp, Id, rank

ORDER By MIN(Date)

This should work in 2000.

julian.rudall Old Hand Points: 301 More actions · Answer 4

Hello,

Thank you to both Mark and NULL for your great replies.

Both sets of code do exactly what they should do 🙂

Cheers

Julian