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
Where dateadd(day,n-1, @minDate )<= @maxDate 
 
)
  
  
, 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
 

 

http://forums.asp.net/p/2093985/6047111.aspx?p=True&t=635975118497246858

Advertisements

One Plan Cache Implementation Technique with Adding Comment to Dynamic Query

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

Add comment section to append to dynamic query to generate different plan cache.

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)
FROM Sales.SalesOrderHeader
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
	INSERT  (Payload) VALUES( src.Payload) 
WHEN NOT MATCHED BY SOURCE  THEN
 Update Set  ID=NULL,V=null
 ;
SELECT  * FROM    @red;

/*
ID	V	Payload
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
-- INSERT  (Payload) VALUES( src.Payload) ;

-- /*
-- ID	V	Payload
--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;

(https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-classic-attach-disk/)

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'
,File=1,NOUNLOAD, REPLACE, STATS = 10
 ,MOVE 'theDB' TO 'O:\MSSQL\theDB.mdf' 
,MOVE 'theDB_log' TO 'O:\MSSQL\theDB_Log.ldf'