# Dense_Rank() Related to Island Gap T-SQL Query

I have used two ways to solve the problem. One is for SQL Server 2012 or above and the other is for SQL Server 2005 or above.

```
create table dataSet (Fruit varchar(50), EatenDate Datetime)
Insert into dataset
SELECT 'Apple' AS Fruit, CAST('20130101' AS DATE) AS EatenDate
UNION ALL
SELECT 'Apple', '20130102'
UNION ALL
SELECT 'Pear', '20130102'
UNION ALL
SELECT 'Pear', '20130103'
UNION ALL
SELECT 'Apple', '20130104'
UNION ALL
SELECT 'Apple', '20130105'
UNION ALL
SELECT 'Pear', '20130106'
UNION ALL
SELECT 'Apple', '20130107'

;With mycte1 AS(

SELECT Fruit, EatenDate
,row_number() Over(Order by EatenDate,Fruit) - row_number() Over(Partition by Fruit Order by EatenDate,Fruit DESC) rnDelta
FROM dataSet
)
,mycte2 as (
Select EatenDate,Fruit
,row_number() Over(Order by EatenDate,Fruit) - row_number() Over(Partition by Fruit, rnDelta Order by rnDelta, EatenDate, Fruit DESC) rnDelta2
FROM mycte1
)
SELECT Fruit, EatenDate, DENSE_RANK()  OVER(ORDER BY rnDelta2) AS Ranking
FROM  mycte2

Order by EatenDate,Fruit
/*
Fruit	EatenDate	Ranking
Apple	2013-01-01	1
Apple	2013-01-02	1
Pear	2013-01-02	2
Pear	2013-01-03	2
Apple	2013-01-04	3
Apple	2013-01-05	3
Pear	2013-01-06	4
Apple	2013-01-07	5
*/

--Query for your question If you are using SQL Server 2012

;With mycte AS(

SELECT Fruit,EatenDate
, CASE WHEN Fruit =lag(Fruit,1)Over(Order by eatendate,FRuit) Then 0 Else 1 END l1

FROM dataSet
)

Select EatenDate,Fruit ,Sum(l1) Over(Order By EatenDate,Fruit) Ranking
FROM  mycte
Order by EatenDate,Fruit

```

# T-SQL Matrix– Sample Code

```

CREATE TABLE [dbo].[test](
[PROD_GRP] [nvarchar](255) NULL,
[CaseType] [nvarchar](255) NULL,
[NumCO] [float] NULL,
[NumCC] [float] NULL,
[NumLR] [float] NULL,
[NumLC] [float] NULL,
[AvgResp] [float] NULL,
[AvgRest] [float] NULL,
[BillTime] [float] NULL,
[NonBillTime] [float] NULL,
[TotalTime] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[test]
([PROD_GRP], [CaseType], [NumCO], [NumCC], [NumLR], [NumLC], [AvgResp], [AvgRest], [BillTime], [NonBillTime], [TotalTime])
VALUES
(N'GLADIATOR', N'CWR', 22, 17, 0, 0, 760, 5224, 0.34, 0.1, 0.43),
(N'GLADIATOR', N'SUP', 811, 809, 44, 21, 21, 1430, 0.63, 6.01, 6.65),
(N'MATRIX', N'CWR', 2, 1, 1, 0, -15689, 123, 0.02, 0, 0.02),
(N'MATRIX', N'SUP', 345, 360, 45, 10, 26, 1817, 11.83, 0.75, 12.58)
GO

;with mycte
as (
SELECT  rn,col,val,sortOrder
FROM
(
select  row_number() Over(Order By Prod_GRP, CaseType) as rn,
Cast(PROD_GRP as nvarchar(50)) PROD_GRP ,	Cast(CaseType as nvarchar(50)) CaseType
,Cast(NumCO as nvarchar(50)) NumCO
,Cast(NumCC  as nvarchar(50)) NumCC
,	Cast(NumLR  as nvarchar(50)) NumLR
,	Cast(NumLC as nvarchar(50)) NumLC
,	Cast(AvgResp as nvarchar(50)) AvgResp
,	Cast(AvgRest as nvarchar(50)) AvgRest
,	Cast(BillTime as nvarchar(50)) BillTime
,	Cast(NonBillTime as nvarchar(50)) NonBillTime
,	Cast(TotalTime as nvarchar(50)) TotalTime

from test

) AS src1
CROSS APPLY (Values('PROD_GRP',PROD_GRP,1),('CaseType', CaseType,2),('NumCO',NumCO,3),	('NumCC',NumCC,4),	('NumLR',NumLR,5)
,('NumLC',NumLC,6),('AvgResp',AvgResp,7),	('AvgRest',AvgRest,8),	('BillTime',BillTime,9),	('NonBillTime',NonBillTime,10),	('TotalTime',TotalTime,11)) d(col,val,sortOrder)
)
SELECT col as PROD_GRP, Max([1]) as GLADIATOR1,Max([2]) as GLADIATOR2, Max([3]) as  MATRIX1,  Max([4]) as  MATRIX2
FROM (SELECT sortOrder, rn,col,val
FROM mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3], [4])) AS pvt
WHERE col<>'PROD_GRP'
GROUP BY col,sortOrder
Order by sortOrder

drop table Test

```

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1f350774-0d04-45e4-b766-0197b161acce/turn-table-on-its-side?forum=transactsql

# “Insert data with time slicing”–Another Time Interval Question

I have come across a well defined question at MSDN about daily time allocation by task. Here is the original question:

+————–+————–+——-+
| Start date | End date | State |
+————–+————–+——-+
|20131205 10:00|20131205 11:00| A |
|20131205 11:00|20131205 12:00| B |
|20131205 12:00|20131205 13:00| C |
+————–+————–+——-+
I would like to insert new state (D) in way that is shown below:

+————–+————–+——–+
| Start date | End date | State |
+————–+————–+——–+
|20131205 10:00|20131205 11:00| A |
|20131205 11:00|20131205 11:20| B |
|20131205 11:20|20131205 11:40| D (NEW)|
|20131205 11:40|20131205 12:00| B |
|20131205 12:00|20131205 13:00| C |
+————–+————–+——–+
It means that if I have other state (B) in this same time as new state (D) then old state (B) should be sliced in time.

Another example:

If the ‘End date’ of D record is 20131205 13:00 result table should look like this:

+————–+————–+——–+
| Start date | End date | State |
+————–+————–+——–+
|20131205 10:00|20131205 11:00| A |
|20131205 11:00|20131205 11:20| B |
|20131205 11:20|20131205 13:00| D (NEW)|
+————–+————–+——–+
How can I achieve this?

I cannot find an easy to do it but I used ten CTEs to in an approach with a technique to solve “gaps and island” questions in the solution.
It is a simple question with a very complicate solution. It took me quick some time to put everything together.
Let me know if you can find an easy way to do it. Thanks.

```
create table test (StartTime datetime, endtime datetime, state varchar(50))
Insert into test   VALUES ('20131205 10:00','20131205 11:00','A'),
('20131205 11:00','20131205 12:00','B'),
('20131205 12:00','20131205 13:00','C')
,('20131205 11:20','20131205 11:40','D NEW')
--,('20131205 11:20','20131205 13:00','D NEW')

declare @maxdt datetime = (Select Max(EndTime) From test)
;WITH mycte AS
(
SELECT   min( StartTime ) eTime   FROM test
UNION ALL
FROM mycte m
WHERE  m.eTime< @maxdt
)

,mycte2 as
(
select  distinct d.starttime, d.state from test  t cross apply (select starttime, state from test union all select endtime, state from test where state like '%New%' ) d(starttime, state)
)

,mycte3 as (Select etime,starttime, state from mycte m  Left JOIN mycte2 m2 on m.etime=m2.starttime )

,mycte4 as (Select m.etime,  COALESCE(  CASE WHEN e.state + f.state IS NOT NULL THEN e.state END, d.state) as state , d.state state0  FROM  mycte3 m
outer apply (select top 1 state from mycte3 WHERE etime<=m.etime and state is not null and state not like '%New%'  Order by etime DESC) d (state)
outer apply (select top 1 state from mycte3 WHERE etime>=m.etime and state is not null and state   like '%New%'  Order by etime ) e (state)
outer apply (select top 1 state from mycte3 WHERE etime<=m.etime and state is not null and state   like '%New%'  Order by etime DESC) f (state)
)

,mycte5 as (select etime,state, state0, row_number() Over(Order By etime) - row_number() Over(Partition by state Order By etime) rn
, row_number() Over(Partition by  state Order By etime DESC) rnNew from mycte4 )

,mycte6 as (select etime, rn, CASE WHEN rnNew=1 Then state0 Else state End as state
, row_number() Over(Partition by  rn Order By etime) rn2  from mycte5 )

,mycte7 as (select etime,state,  row_number() Over( Order By etime) - row_number() Over(Partition by  state Order By etime) rn3   from mycte6  )

,mycte8 as (select etime, state, row_number() Over(Partition by  state,rn3 Order By etime) rn from mycte7 )

,mycte9 as (select  etime, state,  row_number() Over( Order By etime) rn from mycte8 WHERE rn=1 or etime=@maxdt)

Select m91.etime as starttime, m92.etime as endtime, m91.state as state  from mycte9 m91
Left JOIN mycte9 m92  On m91.rn=m92.rn-1
WHERE m91.etime<@maxdt
Order by m91.etime

Option (MaxRecursion 32767)

drop table test

```

# Daily Activity Fill Up With T-SQL (Time Interval) Part 2

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5e019246-2f30-4eb6-91fd-80bbf92957a1/resolve-start-time-and-end-time-in-employee-shift?forum=transactsql

``` create table test (id int identity(1,1), EmpID int, ShiftDate date, StartTime time,  EndTime time, Detail varchar(50))
insert into test values (1,'2013-11-01','9:31 AM', '6:31 PM', 'WORK')
,(1,'2013-11-01','10:39 AM', '11:09 AM', 'Break 1')
,(1,'2013-11-01','11:45 AM', '2:00 PM', 'Lunch')
,(1,'2013-11-01','4:35 PM', '5:00 PM', 'Break 2')
,(1,'2013-11-18','9:30 AM', '6:30 PM', 'WORK')
,(1,'2013-11-18','10:37 AM', '11:00 AM', 'Break 1')
,(1,'2013-11-18','2:00 PM', '3:00 PM', 'Lunch')
,(1,'2013-11-18','4:36 PM', '5:00 PM', 'Break 2')
,(2,'2013-11-18','9:30 AM', '6:30 PM', 'WORK')
,(2,'2013-11-18','10:30 AM', '11:00 AM', 'Break 1')
,(2,'2013-11-18','2:05 PM', '3:00 PM', 'Lunch')
,(2,'2013-11-18','4:31 PM', '5:00 PM', 'Break 2')
,(3,'2013-11-15','9:30 AM', '6:30 PM', 'WORK')
,(3,'2013-11-15','10:30 AM', '12:50 pM', 'Break 1')
,(3,'2013-11-15','4:31 PM', '5:00 PM', 'Break 2')
,(4,'2013-10-18','9:30 AM', '6:30 PM', 'WORK')
,(4,'2013-10-18','10:30 AM', '11:00 AM', 'Break 1')
,(4,'2013-10-18','2:05 PM', '3:00 PM', 'Lunch')
,(4,'2013-10-18','4:31 PM', '5:00 PM', 'Break 2')

declare @maxdt time = (Select Max(EndTime) From test)
;WITH mycte AS
(
SELECT EmpID,ShiftDate, min( StartTime ) eTime, max(detail) as detail  FROM test
Group by EmpID,ShiftDate
UNION ALL
SELECT EmpID, ShiftDate, DATEADD(MINUTE,1,m.eTime) eTime , detail
FROM mycte m
WHERE  m.eTime< @maxdt
)
------ Select * into mytestCalendar  --For optimization, you need to work with a calendar table similar as this CTE
------ from mycte
------Order by eTime

, mycte2 as
(select m.EmpID, m.ShiftDate, eTime, m.detail
,ISNULL(t1.detail, t2.detail) as detail2
,row_number()over(order by m.ShiftDate, eTime) rn from mycte m
Left Join test t1 on m.etime= t1.starttime And m.EmpID=t1.EmpID And m.ShiftDate= t1.ShiftDate
Left Join test t2 on m.etime= t2.endtime And m.EmpID=t2.EmpID And m.ShiftDate= t2.ShiftDate)

,mycte3 as
(
Select m.EmpID, m.ShiftDate, d.etime as etime1,e.etime as etime2
,CASE WHEN  d.EmpID=e.EmpID AND d.ShiftDate=e.ShiftDate AND d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END as detail
,Row_number() Over(Partition By  m.EmpID, m.ShiftDate Order BY m.etime) -  Row_number() Over(Partition By  m.EmpID, m.ShiftDate,
CASE WHEN  d.EmpID=e.EmpID AND d.ShiftDate=e.ShiftDate AND d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END
Order BY  m.etime) rn
FROM mycte2 m
OUTER APPLY (SELECT TOP 1 EmpId, ShiftDate,detail2, etime FROM mycte2
WHERE EmpID=m.EmpID AND ShiftDate=m.ShiftDate AND rn<= m.rn AND detail2 IS NOT NULL ORDER BY rn DESC) d
OUTER APPLY (SELECT TOP 1 EmpID,ShiftDate, detail2, etime FROM mycte2
WHERE EmpID=m.EmpID AND ShiftDate=m.ShiftDate AND rn>= m.rn AND detail2 IS NOT NULL ORDER BY rn ) e
)
--Select * from mycte3
--Order by EmpID, ShiftDate, eTime1
,mycteFinal as (
Select EmpID,   ShiftDate
,Cast(min(etime1) as time(0)) as starttime
,Cast( max(etime2) as time(0)) as endtime
, detail
FROM mycte3
WHERE etime1<=@maxdt
Group by EmpID,ShiftDate, detail,rn
)
Select EmpID, ShiftDate, Convert(varchar(7),starttime,100) starttime
, Convert(varchar(7),endtime,100) endtime
, detail from mycteFinal
Order by EmpID, ShiftDate, Cast(starttime as datetime)
--the max recursion number
Option (MaxRecursion 32767)
drop table test

```

# Daily Activity Fill Up With T-SQL (Time Interval)

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5e019246-2f30-4eb6-91fd-80bbf92957a1/resolve-start-time-and-end-time-in-employee-shift?forum=transactsql

```
declare @maxdt datetime = (Select Max(EndTime) From test)
;WITH mycte AS
(
SELECT min( StartTime ) eTime, max(detail) as detail  FROM test
UNION ALL
FROM mycte m
WHERE  m.eTime< @maxdt
)

, mycte2 as
(select  eTime, m.detail
,ISNULL(t1.detail, t2.detail) as detail2
,row_number()over(order by eTime) rn from mycte m
Left Join test t1 on m.etime= t1.starttime
Left Join test t2 on m.etime= t2.endtime)

,mycte3 as
(
Select  d.etime as etime1,e.etime as etime2
,CASE WHEN d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END as detail
,Row_number() Over(Order BY m.etime) -  Row_number() Over(Partition By
CASE WHEN d.detail2= e.detail2 THEN e.detail2 ELSE m.detail  END
Order BY m.etime) rn
FROM mycte2 m
OUTER APPLY (SELECT TOP 1 detail2, etime FROM mycte2
WHERE rn<= m.rn AND detail2 IS NOT NULL ORDER BY rn DESC) d
OUTER APPLY (SELECT TOP 1 detail2, etime FROM mycte2
WHERE rn>= m.rn AND detail2 IS NOT NULL ORDER BY rn ) e
)

,mycteFinal as (Select Cast(min(etime1) as time(0)) as starttime
, Cast( max(etime2) as time(0)) as endtime
, detail
FROM mycte3
WHERE etime1<=@maxdt
Group by detail,rn
)

Select Convert(varchar(7),starttime,100) starttime
, Convert(varchar(7),endtime,100) endtime
, detail from mycteFinal
Order by Cast(starttime as datetime)

drop table test

/*
starttime	endtime	detail
9:30AM	10:30AM	WORK
10:30AM	11:00AM	Break 1
11:00AM	2:00PM	WORK
2:00PM	3:00PM	Lunch
3:00PM	4:30PM	WORK
4:30PM	5:00PM	Break 2
5:00PM	6:30PM	WORK

*/

```