Table rows:SLN Id StartDate EndDate Duration TimeElapsed1 1 1/1/2012 10.12 1/1/2012 10.13 0day 00:01:00 0-days 00:01:001 1 1/1/2012 10.14 1/1/2012 10.15 0day 00:01:00 0-days 00:03:001 1 1/1/2012 10.15 1/2/2012 10.16 1day 00:01:00 1-days 00:04:00 (total- counter reset) 2 2 1/1/2012 10.12 1/1/2012 10.13 0day 00:01:00 0-days 00:01:002 2 1/1/2012 10.14 1/1/2012 10.15 0day 00:01:00 0-days 00:03:002 2 1/1/2012 10.15 1/2/2012 10.16 1day 00:01:00 1-days 00:04:00 (total- counter reset)
CREATE TABLE #MyDates (SLN INT, ID INT, StartDate DATETIME, EndDate DATETIME ,Duration AS (DATEDIFF(minute, StartDate, EndDate)) ,TimeElapsed BIGINT ,PRIMARY KEY (SLN, StartDate))INSERT INTO #MyDates (SLN, ID, StartDate, EndDate)SELECT 1, 1, '1/1/2012 10:12','1/1/2012 10:13'UNION ALL SELECT 1, 1, '1/1/2012 10:14','1/1/2012 10:15'UNION ALL SELECT 1, 1, '1/1/2012 10:15','1/2/2012 10:16'UNION ALL SELECT 2, 2, '1/1/2012 10:12','1/1/2012 10:13'UNION ALL SELECT 2, 2, '1/1/2012 10:14','1/1/2012 10:15'UNION ALL SELECT 2, 2, '1/1/2012 10:15','1/2/2012 10:16'DECLARE @StartTime DATETIME = '1900-01-01' ,@SLN INT = 0UPDATE #MyDates WITH(TABLOCKX)SET TimeElapsed = DATEDIFF(minute, CASE WHEN @SLN = SLN THEN @StartTime ELSE StartDate END, EndDate) ,@StartTime = CASE WHEN @SLN <> SLN THEN StartDate ELSE @StartTime END ,@SLN = SLNOPTION (MAXDOP 1)SELECT SLN, ID, StartDate, EndDate ,Duration=CAST(Duration / 1440 AS VARCHAR(3)) + ' day' + CASE Duration / 1440 WHEN 1 THEN ' ' ELSE 's ' END + LEFT(DATEADD(minute, Duration % 1440, CAST('00:00' AS TIME)), 8) ,ElapsedTime=CAST(TimeElapsed / 1440 AS VARCHAR(3)) + ' day' + CASE TimeElapsed / 1440 WHEN 1 THEN ' ' ELSE 's ' END + LEFT(DATEADD(minute, TimeElapsed % 1440, CAST('00:00' AS TIME)), 8)FROM #MyDatesDROP TABLE #MyDates