SQL Summing... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Summing...

Author

Topic

lah1308
Starting Member

3 Posts

Posted - 2014-04-29 : 10:29:49

Hi,

I am trying to calculate the total time between various status changes in my database. I have included an example below.

I would like to know the total time between start and stops...so effectively the sum of the date diff between rows 1 and 2, 3 and 4, 5 and 6 etc.

Does anyone know a good way of doing this, preferably without a cursor...?

1 2014-02-21 14:34:11.883 Start
2 2014-02-23 16:06:15.397 Stop
3 2014-02-24 11:36:27.747 Start
4 2014-02-24 12:28:09.373 Stop
5 2014-02-24 13:12:59.377 Start
6 2014-02-25 10:53:13.623 Stop
7 2014-02-25 12:38:02.153 Start
8 2014-02-25 13:51:16.697 Stop
9 2014-02-25 16:23:02.527 Start
10 2014-02-25 16:43:57.767 Stop

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-29 : 11:29:11

If the data in your table always has even numbers for col1 for Stop and odd numbers for start, you can join the table to itself like in the example below. Even if that is not the case, you can do it; you have to write the join carefully to make sure that the appropriate rows are picked up

SELECT SUM(DATEDIFF(s,a.col2, b2.col2))
FROM 
	YourTable a
	INNER JOIN YourTable b ON 
		a.col1+1 = b.col1
WHERE
	a.col3 = 'Start' AND b.col3 = 'Stop'

lah1308
Starting Member

3 Posts

Posted - 2014-04-29 : 12:14:35

Thanks for the response, however... in a real life scenario the id's wont be sequential but rather completely random sp the id+1 wouldnt really work :(

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-29 : 13:00:50

quote:
Originally posted by lah1308

Thanks for the response, however... in a real life scenario the id's wont be sequential but rather completely random sp the id+1 wouldnt really work :(

You can generate a sequence number in code (assuming a start is immediately followed by a stop before anything else chronlogically)

;with cte as
(
   select *,
      row_number() over (order by col2) as RN
   from yourTable
)
SELECT SUM(DATEDIFF(s,a.col2, b2.col2))
FROM 
	cte a
	INNER JOIN cte b ON 
		a.RN+1 = b.RN
WHERE
	a.col3 = 'Start' AND b.col3 = 'Stop'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources