if object_id('tempdb..#Something') is not null drop table #Something create table #Something( Div_Time datetime, Unit_id varchar(25), Filled_time datetime)insert #Somethingselect '1/1/2013 01:00', 'Machine5', '1/1/2013 12:10' union allselect '1/1/2013 01:00', 'Machine5', '1/1/2013 12:15' union allselect '1/1/2013 01:15', 'Machine7', '1/1/2013 12:19' union allselect '1/1/2013 01:15', 'Machine7', '1/1/2013 12:19'select distinct s1.Div_time, s1.Unit_idfrom #Something s1join #Something s2 on s1.Div_Time = s2.Div_Time and s1.Filled_time <> s2.Filled_time
SELECT tt1.ID ,tt1.Mach_Name AS [MachineName] ,tt1.Div_Time AS [1stDivTime] ,tt1.Filled_Time AS [1stFilledTime] ,tt1.Diff1 AS [1stDuration] ,tt2.Div_Time AS [2ndDivTime] ,tt2.Filled_Time AS [2ndFilledTime] ,DATEDIFF(MINUTE,tt2.Div_Time,tt2.Filled_Time) AS [2ndDuration] ,DATEDIFF(MINUTE,tt2.Div_Time,tt2.Filled_Time)-tt1.Diff1 AS [Diff]FROM ( SELECT * ,DATEDIFF(MINUTE,Div_Time,Filled_Time) AS [Diff1] FROM #TempTable ) AS tt1 INNER JOIN #TempTable AS tt2 ON tt1.ID = tt2.ID-1 AND tt1.Reading = 1 AND DATEDIFF(MINUTE,tt2.Div_Time,tt2.Filled_Time)-tt1.Diff1 <> 0
ID MachineName 1stDivTime 1stFilledTime 1stDuration 2ndDivTime 2ndFilledTime 2ndDuration Diff1 Machine5 2013-01-01 01:00:00.000 2013-01-01 12:10:00.000 670 2013-01-01 01:00:00.000 2013-01-01 12:15:00.000 675 57 Machine11 2013-01-01 01:45:00.000 2013-01-01 12:40:00.000 655 2013-01-01 01:45:00.000 2013-01-01 12:28:00.000 643 -12
SELECT Div_time, Unit_id ,TimeDifferenceMin=DATEDIFF(minute, MIN(Filled_Time), MAX(Filled_Time))FROM #SomethingGROUP BY Unit_id, Div_timeHAVING MAX(Filled_Time) <> MIN(Filled_Time)