Count Consecutive Days

[nolanma]

Hi,
I have a report where I need to count consecutive days worked by employee. I have a running count of the days, however, I need to reset the count when the dates are not consecutive.

For Example:

I need to count the days, but I need it to reset after 01/11/13, since 01/12 and 01/13 were not worked.
So, the count should be as follows:

Dates:
01/07/13 - 1
01/08/13 - 2
01/09/13 - 3
01/10/13 - 4
01/11/13 - 5
01/14/13 - 1
01/15/13 - 2
01/16/13 - 3
01/17/13 - 4
01/18/13 - 5

Can someone provide the correct syntax to count the consecutive days? Any assistance is greatly appreciated! Thank you!

 

[nolanma]

I forgot to mention that I am using Cognos Report Studio Version 8.4.
Thanks.

 

[drlex]

I don't have RS, but I have had to deal with this issue before, and used grouping by row numbering in the database to provide a view on the table to join back.

Some SQL code on your example, which may help:

Create Table TestDates(TestDateField smalldatetime)
go
insert into TestDates(TestDateField) values('01/07/2013')
insert into TestDates(TestDateField) values('01/08/2013')
insert into TestDates(TestDateField) values('01/09/2013')
insert into TestDates(TestDateField) values('01/10/2013')
insert into TestDates(TestDateField) values('01/11/2013')
insert into TestDates(TestDateField) values('01/14/2013')
insert into TestDates(TestDateField) values('01/15/2013')
insert into TestDates(TestDateField) values('01/16/2013')
insert into TestDates(TestDateField) values('01/17/2013')
insert into TestDates(TestDateField) values('01/18/2013')
go

Select TestDates.TestDateField, row_number() over (partition by DateGroup order by TestDateField) as DateSequence from TestDates 
inner join 
--THIS SECTION IS THE GROUPING "VIEW"
(
 Select DateGroup, Min(TestDateField) as LowerDate,Max(TestDateField) as UpperDate 
 from 
 (
  Select TestDateField,TestDateField + row_number() over (order by TestDateField desc) as DateGroup 
  from TestDates
 )DateList
 group by DateGroup
)dategrouping 
on TestDates.TestDateField between dategrouping.LowerDate and dategrouping.UpperDate

Drop Table TestDates
go

soi là, soi carré