Dense_Rank() Related to Island Gap T-SQL Query

Question is from MSDN:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b6589664-e351-4d6d-afd4-13309395cd7e/denserank-is-ordering-by-the-partition-column-and-i-dont-want-it-to?forum=transactsql

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'
 
 --Query for your question
;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 am using MS SQL Server 2008)
”
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/212036a1-3e1b-4e7f-b0c5-3aeccea4156b/insert-data-with-time-slicing?forum=transactsql

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
SELECT   DATEADD(minute,1,m.eTime) eTime  
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