Find Date Range Gap Solutions

```

create table blackout(BlackoutId int identity(1,1), Startdate date,Enddate date, CarclassId int)
Insert into blackout values('03/02/2016','03/06/2016', 1) ---dateformat MM/DD/YYYY
,('03/01/2016','03/05/2016', 1)
,('03/03/2016','03/04/2016', 1)
,('05/04/2016','05/05/2016', 1)
,('03/04/2016','03/06/2016', 1)

---Option 1
;with mycte as (
Select distinct dt,CarclassId from blackout
Cross apply (values(StartDate),(EndDate)) d(dt)
)

,mycte1 as  (
SELECT
*,  DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY CarclassId ORDER BY dt), dt) AS grp FROM mycte
)
select CarclassId, min(dt) as [From],max(dt) as [To] from mycte1
group by CarclassId, grp
Order by CarclassId, grp

----Option 2
--Define your date range here. I use min from startdate and max from enddate from your sample table
declare @minDate date=(select min(Startdate) from blackout)
declare @maxDate date=(select max(Enddate) from blackout)
--===== 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, @minDate ) dt from Nums

)

, mycte as (
select dt, CarclassId, row_number() Over(Partition by CarclassId order by dt) rn1 from
(select distinct CarclassId from blackout) t1, myDateCTE
)

,mycte1 as (
SELECT CarclassId, dt, rn1, row_number() Over(Partition by CarclassId order by dt) rn2
FROM mycte t1
WHERE  EXISTS
(SELECT * FROM blackout t2
WHERE t1.dt BETWEEN t2.Startdate AND t2.Enddate and t1.CarclassId=t2.CarclassId)

)

Select CarclassId, Min(m.dt) As Startdate, Max(m.dt) As end_dt
From mycte1 m
Group By CarclassId, m.rn1 - m.rn2
Order by CarclassId

drop table blackout

```

One Plan Cache Implementation Technique with Adding Comment to Dynamic Query

Stan Futoma: https://triplessql.wordpress.com/

The sample he posted in above positng:

```

ALTER PROCEDURE dbo.proc_GetMaxSubTotal @from DATETIME, @to DATETIME
AS
SET NOCOUNT ON;
DECLARE @comment NVARCHAR(MAX)='/* My query variant: '
+ CASE
WHEN DATEDIFF(day,@from,@to)<=1 THEN '1 day'
WHEN DATEDIFF(day,@from,@to)<=7 THEN '1 week'
ELSE 'long time'
END
+ ' */';

DECLARE @sql NVARCHAR(MAX)='
SELECT MAX(SubTotal)
WHERE OrderDate BETWEEN @from_dyn AND @to_dyn;
'+@comment

EXEC sp_executesql @sql, N'@from_dyn DATETIME,@to_dyn DATETIME', @from_dyn=@from, @to_dyn=@to

---execute the procedure with different parameters:

EXEC dbo.proc_GetMaxSubTotal '2006-01-02','2006-01-02';
EXEC dbo.proc_GetMaxSubTotal '2006-01-02','2009-12-31';
EXEC dbo.proc_GetMaxSubTotal '2006-01-02','2006-01-09';

```

Split Roles T-SQL Sample

```

create table t1
(name varchar(10),
roles varchar(100));

insert into t1 values('ABC','role1.role2.role3');
insert into t1 values('xyz','role3.role2.role4');

--===== Create number table on-the-fly

--you can use an auxiliary NUMBER table in your database to simplify your query
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n&lt;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)

select name, substring(roles, n, charindex('.', roles + '.', n) - n)  splitRole from t1

cross apply Nums
Where n &lt;= len(roles) AND substring('.' + roles, n, 1) = '.'

--option 2
select name, role from t1
cross apply(Values (parsename(roles,1)),(parsename(roles,2)),(parsename(roles,3)),(parsename(roles,4)))  d(role)
WHERE role is not null

----Option 3 new function will come in SQL Server 2016
----SQL Server 2016 STRING_SPLIT

--select name, value as role from t1
--cross apply    STRING_SPLIT(roles,'.')

drop table t1

---***********************
--************** Another
set statistics time on
set statistics io on

DECLARE @Reference TABLE
(
RefID int,
ID nvarchar(100)
)

INSERT INTO @Reference
SELECT 1, '422,222'
INSERT INTO @Reference
SELECT 2, '33,333'
INSERT INTO @Reference
SELECT 3, '45,63'

--SELECT * FROM @Reference

--Option 1

--you can use an auxiliary NUMBER table in your database to simplify your query
;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)

select RefID, substring(ID, n, charindex(',', ID + ',', n) - n)  splitIDs from @Reference

cross apply Nums
Where n <= len(ID) AND substring(',' + ID, n, 1) = ','

--Option 2
select RefID, IDs from @Reference
cross apply(Values (parsename(Replace(ID,',','.'),1)),(parsename(Replace(ID,',','.'),2)),(parsename(Replace(ID,',','.'),3)),(parsename(Replace(ID,',','.'),4)))  d(IDs)
WHERE IDs is not null

--Option 3

;WITH cte AS (
SELECT
RefID,
CAST('<t>' + REPLACE(ID, ',', '</t><t>') + '</t>' AS XML) AS p
FROM @Reference

)
SELECT
RefID,
x.value('.','VARCHAR(20)') AS col2
FROM cte
CROSS APPLY p.nodes('//t') a(x)

----Option 4
;with cte(RefID, ID, [level]) as
(
select RefID, CONVERT(nvarchar(max), ID), 0
from @Reference
union all
select RefID, STUFF(ID, 1, CHARINDEX(',', ID), ''), [level] + 1
from cte
where CHARINDEX(',', ID) > 0
)
select RefID, LEFT(ID, CHARINDEX(',', ID + ',') - 1) AS ID
from cte
order by RefID, [level]
OPTION (MAXRECURSION 0);

set statistics time off
set statistics io off

```

https://social.msdn.microsoft.com/Forums/en-US/home?forum=transactsql

Dynamic Pivot Two Columns With Join

```

CREATE TABLE #Table (Code varchar(50),Qty Float,SupName VARCHAR(10),SupQty float,SupPrice decimal(18,3))

insert into #Table VALUES ('Code1',10,'Supplier1',10,20)
insert into #Table VALUES ('Code1',10,'Supplier2',6,25)
insert into #Table VALUES ('Code2',10,'Supplier1',10,15)
insert into #Table VALUES ('Code3',10,'Supplier1',10,30)
insert into #Table VALUES ('Code3',10,'Supplier2',10,28)

DECLARE @cols1 AS NVARCHAR(2000), @cols2 AS NVARCHAR(2000), @colsResult AS NVARCHAR(2000), @cols4 AS NVARCHAR(2000)
,@sql AS NVARCHAR(4000)

SELECT @cols1 = STUFF((select DISTINCT ', ' + quotename(SupName ,']') FROM #Table FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
SELECT @cols2 = STUFF((select DISTINCT ', ' + quotename(SupName +'2',']') FROM #Table FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')

SELECT @colsResult = STUFF((select DISTINCT ', '+ 'Max('+ quotename(SupName)+') as ' + quotename(SupName+'_Qty') +', '+ 'Max('+ quotename(SupName+'2')+') as ' + quotename(SupName+'_Price')
FROM  #Table FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')

Set @sql=N';with mycte1 as (SELECT code, '+ @colsResult
+  ' FROM ( Select Code,Qty,SupName,  SupName+''2'' as SupName2, SupQty,SupPrice FROM #Table) src
PIVOT (Max(SupQty)   for SupName  IN ('+ @cols1 +')) pvt1
PIVOT (Max(SupPrice) for SupName2 IN ('+ @cols2 +')) pvt2
Group by Code)
,mycte2 as (select Code,Qty,SupName, SupQty,SupPrice,row_number() Over(Partition by Code Order by SupPrice) rn
FROM #Table)
,mycte3 as (Select Code,Qty,SupName as LowestSup, SupQty as LowestSupQty,SupPrice as LowestSupPrice from mycte2   Where rn=1)
Select m1.*, m3.Qty,m3.LowestSup,m3.LowestSupQty,m3.LowestSupPrice FROM mycte3 m3 JOIN mycte1 m1 ON m3.code=m1.code
'

exec sp_executesql @sql;

CREATE TABLE #Result (Code varchar(50),Qty Float,Supplier1Qty float,Supplier1Price decimal(18,3),Supplier2Qty float,Supplier2Price decimal(18,3),LowestSup varchar(10),
LowestSupQty float,LowestSupPrice decimal(18,3))
insert into #Result VALUES ('Code1',10,10,20,6,25,'Supplier1',10,20)
insert into #Result VALUES ('Code2',10,10,15,NULL,NULL,'Supplier1',10,15)
insert into #Result VALUES ('Code3',10,10,30,10,28,'Supplier2',10,28)
select * from #Result

drop Table #Result
drop Table #Table

```

https://social.msdn.microsoft.com/Forums/en-US/4b99691b-e2a7-4cc0-a041-e4acb5d60c29/multiple-row-value-as-column-value?forum=transactsql

Count Hourly Customer in a Day

```

create table events (CustomerID int, ArrivedDateTime datetime, DepartDateTime datetime)
Insert into events values (1,'2016-01-01 00:32:00.000', '2016-01-01 01:15:00.000')
,(2, '2016-01-01 00:36:00.000', ' 2016-01-01 07:02:00.000')
,(3 ,'2016-01-01 00:41:00.000', ' 2016-01-01 01:49:00.000')
,(4,'2016-01-01 00:51:00.000', ' 2016-01-01 09:00:00.000')
,(5,'2016-01-01 00:58:00.000', ' 2016-01-01 09:10:00.000')
,(6,'2016-01-01 01:27:00.000', ' 2016-01-01 14:05:00.000')

;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(hour,n,(select Cast(Cast(min(ArrivedDateTime) as date) as datetime)
from events)) dt
from Nums
--(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23) ) d(n)

)

--select Cast(dt as Date) [Date], datepart(hour,dt)-1 [Hour],count(*) CustomersCnt
--from  events e left join myDateCTE c
-- on c.dt between e.ArrivedDateTime and e.DepartDateTime
--Group by dt

--select Cast(dt as Date) [Date], datepart(hour,dt)  [Hour],  CustomerID
--from  myDateCTE c  left join events e
-- on c.dt between e.ArrivedDateTime and e.DepartDateTime

select Cast(dt as Date) [Date], datepart(hour,dt)  [Hour]
,  Sum(Case when CustomerID is not null then 1 else 0 end)  CustomerCnt
from  myDateCTE c  left join events e
on c.dt between e.ArrivedDateTime and e.DepartDateTime
Group by dt

Drop table events

```

https://social.msdn.microsoft.com/Forums/en-US/8a5f5de5-6fa2-420e-bc0e-68d978479ef2/repeat-rows-by-datediff-value?forum=transactsql

A Code Sample with Merge

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7ddcefe1-4d16-4380-b146-ce262129d5f6/small-query-several-doubts?forum=transactsql#ded2a8a8-4ee5-4423-970b-b2a7d8b26662

```

DECLARE @red TABLE ( ID INT, V INT, Payload INT );

INSERT  INTO @red
VALUES  ( 1, 1, 1 ),
( 2, 2, 2 ),
( 3, 1, 3 ),
( 4, 2, 4 ),
( 5, 1, 5 ),
( 6, 2, 6 ),
( 7, 1, 7 ),
( 8, 2, 8 ),
( 9, 1, 9 );

MERGE @red tgt
USING (
SELECT	*
FROM	@red
WHERE	[v] &lt; (SELECT ISNULL(MAX([v]), -1) FROM  @red)
) src ON tgt.id=src.id

WHEN NOT MATCHED BY TARGET THEN
WHEN NOT MATCHED BY SOURCE  THEN
Update Set  ID=NULL,V=null
;
SELECT  * FROM    @red;

/*
1	1	1
NULL	NULL	2
3	1	3
NULL	NULL	4
5	1	5
NULL	NULL	6
7	1	7
NULL	NULL	8
9	1	9

*/

--  MERGE @red tgt
--Using ( select * from  @red  WHERE  [v] &gt;= (SELECT Isnull(Max([v]), -1) from  @red ) ) src on tgt.id=src.id

--When matched then
--Delete
--When not matched by target then

-- /*
--1	1	1
--3	1	3
--5	1	5
--7	1	7
--9	1	9

--*/

-- select * from @Red

```

Edit:
A post with interesting MERGE samples:
Cool MERGE features you may not know about

Set up VM SQL Server from Azure

Step 1: Create a VM from the gallary with your choice that include SQL Server version you need;

Step 2: Remote log in to the VM and change Windows permissions to allow  user(s ) to be able to “Perform volume maintenance tasks”;

(secpol.msc >>User Rights Assignment: https://sqlserverperformance.wordpress.com/2009/11/01/two-very-important-configuration-settings-for-sql-server-20052008/  )

Step 3: Add a data disk (Drive O in this sample) from Azure portal to the VM and initialize the disk from the VM;

Step 4: Restore databases from backups either from copied local backup files (may need to move bak file large than 2G) or restore directly from an Azure storage account.

```

use master
go

OPEN Master Key Decryption  BY PASSWORD = 'myifbfpass\$2014ForAzure'
Go

Restore DATABASE theDB
FROM URL = 'https://xyz.blob.core.windows.net/aserverInstance/theDB_3bc8ab8d4f8540acbfd7da39fc575b2e_20160323111645-05.bak'
/* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/
WITH CREDENTIAL = 'myAzureStorage'