CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2008-r2
Ruhaan
Ruhaan

Reputation: 174

SQL: Difference Between Current Row of StartTime Column And Previous Row of EndTime Column

I have a Table in which there are multiple columns but I need to find the Difference Between Current Row of StartTime Column And Previous Row of EndTime Column.

Example is the Following output.

Batch Number    Start Time  End Time        Difference

100004          8:00:00     8:03:30        
100005          8:05:00     8:07:00         00:01:30
100006          8:08:40     8:15:00         00:01:40
32141           8:18:00     8:22:45         00:03:00
84230           8:25:10     8:33:42         00:02:25
23444           8:40:00     8:43:00         00:06:18
100001          8:50:00     8:52:00         00:07:00

I am new to SQL and am using SQL SERVER 2008 R2.

Please help me to get the output in Simple Select Query.

Upvotes: 1

Views: 305

Answers (2)

vercelli
vercelli

Reputation: 4767

I follow this link http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/ This will give you the difference in seconds.

;with cteMain as (
    select *, ROW_NUMBER() over (order by Start_time) sn
      from table)
select m.batch_number, sLag.End_date,  m.Start_time, convert(varchar,DateAdd(Second,DATEDIFF(SECOND, sLag.End_date, m.Start_time),0),108) as time_diff
  from cteMain as m LEFT OUTER JOIN cteMain AS sLag ON sLag.sn = m.sn-1
order by m.batch_number

Upvotes: 1

Jatin Patel
Jatin Patel

Reputation: 2104

CREATE TABLE #Batches
(
    BatchID     INT,
    StartTime   Datetime,
    EndTime     Datetime,
)
INSERT INTO #Batches
VALUES
(100004,'2016-05-16 08:00:00','2016-05-16 08:03:30'),
(100005,'2016-05-16 08:05:00','2016-05-16 08:07:00'),
(100006,'2016-05-16 08:08:40','2016-05-16 08:15:00'),
(32141 ,'2016-05-16 08:18:00','2016-05-16 08:22:45'),
(84230 ,'2016-05-16 08:25:10','2016-05-16 08:33:42'),
(23444 ,'2016-05-16 08:40:00','2016-05-16 08:43:00'),
(100001,'2016-05-16 08:50:00','2016-05-16 08:52:00')

;WITH CTE AS
(
    SELECT
    BatchID,
    StartTime,
    EndTime,
    ROW_NUMBER() OVER (ORDER BY StartTime) AS Seq
    FROM #Batches
)
SELECT
    b.BatchID,
    b.StartTime,
    b.EndTime,
    CONVERT(VARCHAR(20), DATEADD(SECOND,DATEDIFF(SECOND, bl.EndTime,b.StartTime),0),108) AS Diff,
    DATEADD(SECOND,DATEDIFF(SECOND, bl.EndTime,b.StartTime),0) AS DiffDT
FROM CTE AS b
LEFT OUTER JOIN CTE AS bl ON bl.Seq = b.Seq - 1 -- Last batch   
ORDER BY b.StartTime

Upvotes: 4

Related Questions