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

 

Recursive CTE Sample (Multiple entry points)

  

  Create TABLE test (ID int NOT NULL PRIMARY KEY,Check_GKey int NULL,GKey int NULL)

INSERT INTO test(ID,GKey) VALUES (8,7),
(11	,7),
(40	,24),
(7	,7),
(24	,6),
(1	,1),
(99	,8),
(23	,2),
(2	,2),
(45	,45),
(6,6)

;WITH rmycte AS
(
SELECT ID,GKey as Check_GKey, GKey FROM test t
WHERE  EXISTS(SELECT 1 FROM test WHERE ID<=t.ID AND GKey=t.ID)
UNION ALL
SELECT t.ID, r.Check_GKey,T.GKey FROM rmycte r JOIN test t 
ON r.ID=t.GKey AND r.ID<>t.ID
)
SELECT * FROM rmycte 
ORDER BY ID

Drop table test

/*
ID	Check_GKey	GKey
1	1	1
2	2	2
6	6	6
7	7	7
8	7	7
11	7	7
23	2	2
24	6	6
40	6	24
45	45	45
99	7	8

*/

 

Dynamic Pivot Sample

Create table yourtable (itemID INT, part CHAR(1))

INSERT INTO yourtable VALUES(1,'A'),(1,'B'),(2,'A'),(2,'A'),(2,'A'),(3,'C')

DECLARE @colsSorted AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

select @colsSorted = STUFF((select DISTINCT ', '
+ quotename( Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted

Set @sql=N'SELECT ItemID, '+ @colsSorted + ' FROM (
Select ItemID,Part, Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) as Cols
FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt'

exec sp_executesql @sql;

drop table yourtable

 

 

 

--Another

create table test (ADMISSIONNO int,FULLNAME varchar(50), SUBJECT varchar(50), GRADE Char(1))
Insert into test values

(3886,'ALIYU USMAN','MATHEMATICS','A'),
(3886,'ALIYU USMAN','ENGLISH','B'),
(3886,'ALIYU USMAN','BIOLOGY','B'),

(3887,'JOHN INNOCENT','MATHEMATICS','A'),
(3887,'JOHN INNOCENT','ENGLISH','A'),
(3887,'JOHN INNOCENT','BIOLOGY','A')

---Case
Select ADMISSIONNO
,Max(Case WHEN Subject='MATHEMATICS' Then Grade End) MATHEMATICS
,Max(Case WHEN Subject='ENGLISH' Then Grade End) ENGLISH
,Max(Case WHEN Subject='BIOLOGY' Then Grade End) BIOLOGY
from test
Group by ADMISSIONNO

--Pivot
Select ADMISSIONNO,MATHEMATICS, ENGLISH , BIOLOGY
from test
Pivot (Max(Grade) For SubJect IN ([MATHEMATICS],[ENGLISH],[BIOLOGY])) pvt

--Dynamic Pivot

DECLARE @cols AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

SELECT @cols = STUFF((select DISTINCT ', ' + quotename(SubJect ,']')
FROM test
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @cols

Set @sql=N'SELECT ADMISSIONNO, '+ @cols + ' FROM test PIVOT (Max(Grade) for Subject IN ('+ @cols +')) pvt'

exec sp_executesql @sql;
drop table test

The quote_character in QUOTENAME function

The the quote_character is defined as one character but it seems to work when you put both brackets. It will take only the first one and it will work either left bracket or right bracket. The default for the quote_character is [.

  

  declare @name varchar(10)='Test Name'

 select  
 QUOTENAME(@Name,'('), 
 QUOTENAME(@Name,')'),
  QUOTENAME(@Name,')('), 
 QUOTENAME(@Name,'()'),
  QUOTENAME(@Name)
 

 

https://msdn.microsoft.com/en-us/library/ms176114.aspx?f=255&MSPPError=-2147217396