Find out unique counts based on consecutive days


  

Declare @Test TABLE (id int,[Date] date)


INSERT INTO @Test Values
(100,'07/01/2015')
,(100,'07/02/2015')
,(100,'07/03/2015')
,(100,'08/01/2015')
,(100,'08/02/2015')
,(100,'08/15/2015')  

;with mycte as (
select   DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Date]) ,[Date]) grp 
from @Test)

Select Count(Distinct grp) cnt 
from mycte



--3
 

https://social.msdn.microsoft.com/Forums/en-US/0a019eac-016e-44da-bc6f-d278628d27f2/find-out-unique-counts-based-on-consecutive-days?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s