Island Question Variation


 

if object_id('test','U') is not null drop table test 
create table test (MBR_ID int,  DS varchar(10))
Insert into test values
(1,'2011-07-15'), (1,'2011-07-21'),(1,'2011-07-29'),(1,'2011-08-04'),(1,'2011-08-11'),(1,'2011-08-25'),(1,'2011-09-01'),(1,'2011-09-21')
,(1,'2012-01-11'),(1,'2012-03-15'),(1,'2012-06-04'),(1,'2012-10-11'),(1,'2013-02-14'),(1,'2013-06-18'),(1,'2013-07-08'),(1,'2013-07-15')
,(1,'2013-07-17'),(1,'2013-08-07'),(1,'2013-08-14'),(1,'2013-08-28'),(1,'2013-09-11'),(1,'2013-09-25'),(1,'2013-11-20')
,(1,'2013-12-04'),(1,'2013-12-17'),(1,'2014-01-14'),(1,'2014-01-29'),(1,'2014-02-25'),(1,'2014-02-26'),(1,'2014-03-12')
,(1,'2014-03-19'),(1,'2014-03-22'),(1,'2014-03-24')
 

;with mytest as (
--move date to the end of the month
select MBR_ID, eomonth(ds) as ds from test)
,mycte as (
SELECT MBR_ID, ds,  lag(ds, 1) Over(partition by MBR_ID  ORDER BY ds) dslag
 ,Case when datediff(month,lag(ds, 1) Over(partition by MBR_ID ORDER BY ds), ds) <=2 
 Then 0 Else datediff(month,ds,lag(ds, 1) Over(partition by MBR_ID ORDER BY ds) ) End diff
 
FROM mytest)

---get group grn
,mycte1 as (
Select MBR_ID, ds, SUM(diff) Over(Order by ds) grn  from mycte )

--Get YYYY-MM
,mycte2 as (
select MBR_ID, Convert(varchar(7),min(ds),126) START_DT, Convert(varchar(7),max(ds),126)  END_DT 
from mycte1 
Group by MBR_ID,grn)

--Sort
Select MBR_ID, START_DT,END_DT from mycte2 
Order by Cast(START_DT+'-01' as date)
 
 /*
 MBR_ID	START_DT	END_DT
1	2011-07	2011-09
1	2012-01	2012-03
1	2012-06	2012-06
1	2012-10	2012-10
1	2013-02	2013-02
1	2013-06	2014-03

 */


 

https://social.msdn.microsoft.com/Forums/en-US/1a8ff1d3-fdbb-4cb8-b596-249cded7b874/sql-query-help?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