Finding Start and End Time From a Series Datetime Column (T-SQL)


I came across this question at MSDN T-SQL forum:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/339af246-12e9-4c82-8d26-c3f23f126017
Here is my solution:



IF OBJECT_ID('tempdb..#P') IS NOT NULL
	DROP TABLE tempdb..#P
GO	
CREATE TABLE #P
(
	PID	INT IDENTITY(1,1)
	, DT	DATETIME
	, BT	BIT
)
GO
INSERT #P
	SELECT '2012-01-02 23:18:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:23:07.000', 1 UNION ALL	
	SELECT '2012-01-02 23:28:29.000', 0 UNION ALL		
	SELECT '2012-01-02 23:29:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:30:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:31:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:32:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:33:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:34:07.000', 0 UNION ALL
	SELECT '2012-01-02 23:35:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:36:07.000', 0 UNION ALL				
	SELECT '2012-01-02 23:37:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:38:07.000', 1 UNION ALL
	SELECT '2012-01-02 23:39:07.000', 1 UNION ALL		
	SELECT '2012-01-02 23:40:07.000', 0
GO
--SELECT * FROM #P
--GO
/*
--Expected results
StartTime					EndTime
2012-01-02 23:18:07.000		2012-01-02 23:28:29.000
2012-01-02 23:29:07.000		2012-01-02 23:34:07.000
2012-01-02 23:35:07.000		2012-01-02 23:36:07.000
2012-01-02 23:37:07.000		2012-01-02 23:40:07.000

*/	


;with mycte as
(
SELECT T.PID, BT,DT, row_number()over(order by DT desc) rn
FROM #P T)

,mycte2
AS(
SELECT m.pid,m.DT,m.BT,  d.DT AS DT2, row_number()over(PARTITION BY d.DT Order by m.DT ASC) rn2 FROM mycte m
OUTER APPLY (SELECT TOP 1 DT FROM mycte
WHERE rn<= m.rn AND BT =0
ORDER BY rn desc) d
)


SELECT DT AS StartTime,DT2 AS EndTime FROM mycte2
WHERE rn2=1
ORDER BY DT



-----***********First Try******************
--;with mycte as
--(
--SELECT T.PID, BT,DT,CASE WHEN T.BT=1 THEN NULL ELSE T.DT END AS DT2, row_number()over(order by DT desc) rn
--FROM #P T)

--,mycte2
--AS(
--SELECT m.pid,m.DT,m.BT,d.DT2, row_number()over(PARTITION BY d.dt2 order by m.DT ) rn2 FROM mycte m
--OUTER APPLY (SELECT TOP 1 * FROM mycte
--WHERE rn<= m.rn AND BT =0
--ORDER BY rn desc) d
--)

----SELECT * FROM mycte2

--SELECT   DT AS StartTime,DT2 AS EndTime FROM mycte2
--WHERE rn2=1
--ORDER BY DT


Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s