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
SELECT DATEADD(MINUTE,30,m.eTime) eTime , detail
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

*/

 

Row_Number In Action — a Sample Code

CREATE TABLE test (Person VARCHAR(20), Action VARCHAR(10), [Date] DATE)
INSERT INTO test
VALUES (‘MAX’,’Sale’,’20100720′),
(‘MAX’,’Sale’,’20101026′),
(‘MAX’,’Sale’,’20101103′),
(‘MAX’,’Sale’,’20110401′),
(‘MAX’,’No Sale’,’20110528′),
(‘MAX’,’Sale’,’20110613′),
(‘MAX’,’Sale’,’20110711′),
(‘MAX’,’Sale’,’20110819′),
(‘MAX’,’No Sale’,’20111001′),
(‘MAX’,’No Sale’,’20111209′),
(‘MAX’,’Sale’,’20120201′),
(‘MAX’,’No Sale’,’20120201′),
(‘MAX’,’Sale’,’20120207′),
(‘MAX’,’Sale’,’20120213′),
(‘MAX’,’No Sale’,’20130706′);
;with mycte as
(
select *, row_number() Over(order By [Date],Action Desc) rn1
, row_number() Over(partition by Action order By Date, aCTION desc) rn2
, row_number() Over(order By [Date],Action DESC ) – row_number() Over(Partition by Action Order By [Date] ) rn3
from test)
, mycte2 as
( Select *,row_number() Over(partition by rn3 order By CASE WHEN Action=’Sale’Then [Date] END, CASE WHEN Action=’No Sale’Then [Date] END DESC) rn4
, row_number() Over(partition by Action, rn3 order By Action ) rn5
from mycte )
SELECT Person,Action,[Date],’Y’ AS flag
FROM mycte2
WHERE rn4 = 1 OR rn5 = 1
ORDER BY [Date], ACTION DESC

DROP TABLE test

/*
Person Action Date flag
MAX Sale 2010-07-20 Y
MAX No Sale 2011-05-28 Y
MAX Sale 2011-06-13 Y
MAX No Sale 2011-12-09 Y
MAX Sale 2012-02-01 Y
MAX No Sale 2012-02-01 Y
MAX Sale 2012-02-07 Y
MAX No Sale 2013-07-06 Y

*/

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/70d0a54f-a7c1-42c7-afe0-300ecd6d57bd/sql-query?forum=transactsql

Dynamic Pivot On Two Columns

Create table table1 (ID int, Date datetime, Test varchar(10))
INSERT INTO TABLE1 VALUES (81540,'2013-07-27 00:00:00.000', 'CT'), 
(81540,'2013-07-24 00:00:00.000', 'IMP'),
(81540,'2013-07-27 00:00:00.000', 'MRR'),
(81540,'2013-07-27 00:00:00.000', 'MRR'),
(81540,'2013-07-27 00:00:00.000', 'MRR2'),
(81541,'2013-07-24 00:00:00.000', 'IMP'),
(81541,'2013-07-27 00:00:00.000', 'ARR'),
(81541,'2013-07-27 00:00:00.000', 'BRR')
DECLARE @colTest AS NVARCHAR(4000)=''
DECLARE @colDate AS NVARCHAR(4000)=''
DECLARE @Cols AS NVARCHAR(4000)=''
DECLARE @sql AS NVARCHAR(max)

 ;With mycte as
 (Select distinct row_number() OVer(Partition By ID Order by Test, Date) rn from table1)
SELECT @colDate= (SELECT stuff( (SELECT ',['+ Cast(rn as varchar(10)) +']'FROM mycte  FOR XML PATH('')),1,1,''))
, @colTest= (SELECT stuff( (SELECT ',['+ Cast(rn+10 as varchar(10)) +']'FROM mycte  FOR XML PATH('')),1,1,''))
, @Cols= (SELECT stuff( (SELECT ', Max(['+ Cast(rn as varchar(10)) +']) as [Date' +Cast(rn as varchar(10))+']'+', Max(['+ Cast(rn+10 as varchar(10)) +']) as [Test' +Cast(rn as varchar(10))+']' FROM mycte  FOR XML PATH('')),1,1,''))
 


--print @colTest
--print @colDate
--print @Cols

--;with mycte1 as
--(Select *, row_number() OVer(Partition By ID Order by Test, Date) rn from table1)
--Select ID,  Max([1]) as Date1,  Max([11]) as Test1, Max([2]) as Date2,  Max([12]) as Test2, Max([3]) as Date3,  Max([13]) as Test3, Max([4]) as Date4
--, Max([14]) as Test4 from (SELECT ID, Date, Test, rn, rn+10 as rn10 from mycte1 ) src
--PIVOT (Max(Date) For rn IN ([1],[2],[3],[4])) pvt
--PIVOT (Max(Test) For rn10 IN ([11],[12],[13],[14])) pvt2
--GROUP BY ID
 

 
SET @sql=';with mycte1 as (Select *, row_number() OVer(Partition By ID Order by Test, Date) rn from table1) SELECT [ID], ' + @Cols 
+ ' FROM (SELECT ID, Date, Test, rn, rn+10 as rn10 from mycte1) src
 PIVOT (MAX(Date) FOR [rn] IN ('+ @colDate + ')) AS pvt
 PIVOT (MAX(Test) FOR [rn10] IN ('+ @colTest + ')) AS pvt10 
 Group By ID '
--print @sql
EXEC (@sql);

drop table table1

30-Minute Aggregate Solution with T-SQL

create table orders (OrderDate datetime,OrderState int)
Insert into orders values (getdate(),1),(dateadd(minute,20,getdate()),1),(dateadd(minute,25,getdate()),1),(dateadd(minute,35,getdate()),1)
,(getdate(),2),(dateadd(minute,20,getdate()),2),(dateadd(minute,170,getdate()),2),(dateadd(minute,40,getdate()),2),(dateadd(minute,45,getdate()),2)

 declare @dt datetime = (Select Max(OrderDate) From Orders)
 ;WITH mycte AS
(
SELECT min(Dateadd(hour,DATEDIFF(hour,0,OrderDate),0)) OrderDate, OrderState
FROM orders
Group By OrderState

UNION ALL
SELECT DATEADD(MINUTE,30,m.OrderDate) EndTime, m.OrderState
FROM mycte m
WHERE m.OrderDate< @dt
)


, mycte1 as
 (
Select dateadd(minute
,Case When datepart(minute,OrderDate)<=15 Then 0
When datepart(minute,OrderDate)>15 ANd datepart(minute,OrderDate)<=30 Then 30
 When datepart(minute,OrderDate)>30 ANd datepart(minute,OrderDate)<=45 Then 30
 Else 60 End ,DATEADD(hour, DATEDIFF(hour,0,OrderDate),0) ) as dt30MinutesInterval,

OrderState
from dbo.Orders )

Select m.OrderState, m.OrderDate,[Order Count] = COUNT(m1.dt30MinutesInterval)
 from mycte m Left join mycte1 m1 On m.OrderDate = m1.dt30MinutesInterval AND m.OrderState=m1.OrderState
 Group By m.OrderDate ,m.OrderState
 order by m.OrderState, m.OrderDate
drop table orders

Hide Column With Null Values using SQL server

I don’t think this requirement should be implemented in SQL end.By using dynamic SQL with UNIPIVOT to get the result:

  
DECLARE @colsCast NVARCHAR(2000)
, @cols NVARCHAR(2000)
, @sql1 NVARCHAR(4000)
, @sql2 NVARCHAR(4000)
, @sql3 NVARCHAR(4000)
, @Schema_Name NVARCHAR(4000) ='dbo'
, @TABLE_NAME NVARCHAR(4000) ='Source'
 

SELECT @colsCast = COALESCE(@colsCast + ', ', '') + 'CAST('+ Quotename(column_Name)+' AS NVARCHAR(4000)) AS '+ Quotename(column_Name)
, @cols = COALESCE(@cols + ', ', '') + Quotename(column_Name)  
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME ANd Table_Schema=@Schema_Name

--print @cols 

SET @sql1='IF OBJECT_ID(N''dbo.stagingTable'',N''U'') IS NOT NULL
Begin
DROP Table dbo.stagingTable
END
Begin
SELECT  * INTO stagingTable
FROM (SELECT '+ @colsCast+ ' FROM  '+ @Schema_Name+'.'+ Quotename(@TABLE_NAME) + ') src
UNPIVOT (val For col IN ( '+ @cols+ ')) unpvt  
END'
EXEC sp_executesql @sql1


SET  @sql2 = (SELECT distinct  stuff((select ',' + m1.col
from (select distinct col as col from dbo.stagingTable) m1 
 for XML PATH('')),1,1,'') 
from( select distinct col as col from dbo.stagingTable) m Group By col )
 
 
 --print @sql2

 Set @sql3=N'SELECT '+@sql2+ ' FROM  '+ @Schema_Name+'.'+ @TABLE_NAME

 EXEC sp_executesql @sql3

--http://forums.asp.net/p/1931738/5501150.aspx?p=True&t=635139931216804858&pagenum=1

Common Table Expression (CTE) with a View in SQL Server 2005/8

You cannot use Common Table Expression directly inside a View with SQL Server 2005/8  but you can use an inline table function to wrap the CTE returned as table. You can call the function inside the view as you would with a table.

Here is the sample code with my early recusive CTE sample;

Step 1 to create the function:

 

CREATE FUNCTION [dbo].[mytableFunction1]

()

RETURNS TABLE

AS

RETURN

(

with mycte as

(

SELECT 1 as i, CHar(ascii('A')) as myletter

UNION ALL

select i+1 , Char(ascii('A')+i) FROM mycte WHERE i<26

)

SELECT * FROM mycte

)

Step 2 to create the view:

CREATE VIEW [dbo].[View_CTE]

AS

SELECT i, myletter    FROM dbo.mytableFunction1() AS mytableFunction1_1

Edit: Thanks to James for pointing the error from the posting.
Here is the link from MSDN:
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx