Find Customer Each Stay (Gaps and Islands with TSQL)


  

  CREATE TABLE Test (CustomerNumber INT,ArrivalDate DATE,DepartureDate DATE,Days INT)

INSERT INTO Test
VALUES     (1120,'10/24/2014','11/21/2014',28),
           (1120,'11/21/2014','12/19/2014',28),
           (1120,'12/19/2014','1/30/2015', 42),
           (1120,'1/30/2015','3/13/2015',42),
		   (1130,'5/8/2015','6/5/2015',28),
           (1130,'6/5/2015','7/1/2015',26),
           (1130,'7/1/2015','8/8/2015', 38),
           (1140, '8/8/2015','9/26/2015',49),
           (1140,'9/29/2015','10/20/2015',21),
           (1140,'10/25/2015','11/5/2015',11) 

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
Union all 
Select n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
,myDateCTE as
(
select dateadd(day,n-1,(select min(ArrivalDate) from test)) dt ,CustomerNumber
from Nums,(Select distinct  CustomerNumber from test) t
Where dateadd(day,n-1,(select min(ArrivalDate) from test))<=(select max(DepartureDate) from test)
)
 

 ,mycte2 as (
SELECT CustomerNumber, dt, DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY CustomerNumber ORDER BY dt), dt) AS grp
FROM myDateCTE t1
   WHERE  EXISTS 
     (SELECT CustomerNumber, ArrivalDate,DepartureDate FROM test t2 
       WHERE t1.dt BETWEEN t2.ArrivalDate AND t2.DepartureDate 
	   and t1.CustomerNumber=t2.CustomerNumber) )
 
Select CustomerNumber, Datediff(DAY, Min(dt) , Max(dt)) as [EachStay]
From mycte2  
Group By CustomerNumber,grp

drop table   Test

 
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