Database Challenges

--Pragnesh_Patel_tsqlchallenge_23_v4.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--
--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

;WITH CTE_AutoRow ( RowID, AuditTime, IsOnLine )
	AS (
			SELECT ROW_NUMBER() OVER (ORDER BY AuditTime ASC)
				, AuditTime
				, IsOnLine
			FROM TC23
		)
	, CTE_FILTER
	AS (

			SELECT 
				Null AS PrevRowID,  Null AS PrevAuditTime,  Null AS PrevIsOnLine,
				RowID, AuditTime, IsOnLine
			FROM CTE_AutoRow
			WHERE CTE_AutoRow.RowID = 1

			UNION ALL
			SELECT 
				Prev.RowID		AS PrevRowID, 
				Prev.AuditTime	AS PrevAuditTime, 
				CAST( Prev.IsOnLine AS Int ) AS PrevIsOnLine,
				Curr.RowID, 
				Curr.AuditTime, 
				CAST( Curr.IsOnLine AS Int ) AS IsOnLine
			FROM CTE_AutoRow AS Curr
				INNER JOIN CTE_AutoRow AS Prev ON Prev.RowID + 1 = Curr.RowID

			UNION ALL
			SELECT 
				RowID AS PrevRowID,  AuditTime AS PrevAuditTime,  IsOnLine AS PrevIsOnLine,
				Null AS RowID,  Null AS AuditTime,  Null AS IsOnLine
			FROM CTE_AutoRow
			WHERE CTE_AutoRow.RowID = ( SELECT MAX(RowID) FROM CTE_AutoRow )

		)
	, CTE_NewAutoRow
	AS ( 
			SELECT 
				ROW_NUMBER() OVER 
					(ORDER BY 
						 CASE WHEN Curr.PrevRowID IS Null
								THEN CASE WHEN DATEADD( hh, DATEDIFF( hh, 0, Curr.AuditTime ) + 0, 0 ) = Curr.AuditTime
											THEN DATEADD( hh, DATEDIFF( hh, 0, Curr.AuditTime ) + -1, 0 )
											ELSE DATEADD( hh, DATEDIFF( hh, 0, Curr.AuditTime ) + 0, 0 )
										END
							 WHEN Curr.RowID IS Null
								THEN DATEADD(hh, DATEDIFF(hh, 0, Curr.PrevAuditTime ) + 1, 0 )
							ELSE
								-- Curr.AuditTime
								Curr.PrevAuditTime + CONVERT( VARCHAR, CONVERT( DATETIME, CAST( CAST( CAST( Curr.AuditTime - Curr.PrevAuditTime AS FLOAT ) AS DATETIME ) AS FLOAT ) / 2,  120 ), 120 )
						  END
					 ASC) AS RowID

				, CASE WHEN Curr.PrevRowID IS Null
						THEN CASE WHEN DATEADD( hh, DATEDIFF( hh, 0, Curr.AuditTime ) + 0, 0 ) = Curr.AuditTime
									THEN DATEADD( hh, DATEDIFF( hh, 0, Curr.AuditTime ) + -1, 0 )
									ELSE DATEADD( hh, DATEDIFF( hh, 0, Curr.AuditTime ) + 0, 0 )
								END
					 WHEN Curr.RowID IS Null
						THEN DATEADD(hh, DATEDIFF(hh, 0, Curr.PrevAuditTime ) + 1, 0 )
					ELSE
						Curr.PrevAuditTime + CONVERT( VARCHAR, CONVERT( DATETIME, CAST( CAST( CAST( Curr.AuditTime - Curr.PrevAuditTime AS FLOAT ) AS DATETIME ) AS FLOAT ) / 2,  120 ), 120 )
				  END AS TimeFrom

				, IsOnLine

			FROM CTE_FILTER AS Curr
			WHERE IsNull( IsOnLine, -1 ) <> IsNull( PrevIsOnLine, -1 )
		)


SELECT Curr.TimeFrom AS TimeFrom
	, Next.TimeFrom AS TimeTo
	, CASE Curr.IsOnLine 
		WHEN 0 THEN 'No'
		WHEN 1 THEN 'Yes'
		ELSE ''
	  END AS IsOnLine
FROM CTE_NewAutoRow AS Curr
	INNER JOIN CTE_NewAutoRow AS Next ON Next.RowID = Curr.RowID + 1
OPTION (MaxRecursion 0, MAXDOP 1)

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.