Sum times with condition - 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 2008 Forums

Transact-SQL (2008)

Sum times with condition

Author

Topic

orenshto
Starting Member

3 Posts

Posted - 2015-03-10 : 04:54:58

Hey,
I have a table which contains speed samples of car. the table has the following structure:

DateTime | Speed
07:50:00 | 85
08:00:00 | 90
08:01:00 | 92
08:03:00 | 96
08:10:00 | 88

and so on..

Pay attention that the sample's interval is not constant.
Assume that if sample was taken at 08:00:00 at speed of 95 KPH and the next one was taken at 08:10:00 at speed of 88 KPH - the car speed for these 10 minutes was 95.
I need a query which summarize the total time the car was at speed of minimum 90 KPH.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-10 : 05:17:52

[code]
DECLARE @tSample TABLE
( [DateTime] TIME
,Speed TINYINT
)

INSERT INTO @tSample
VALUES('07:50:00',85)
,('08:00:00',90)
,('08:01:00',92)
,('08:03:00' , 96)
,('08:10:00' , 88)

SELECT
--A.[Datetime] AS StartDateTime
--,COALESCE(B.EndDateTime,A.[Datetime] ) AS EndDateTime
--,
SUM(DATEDIFF(MINUTE , A.[Datetime],COALESCE(B.EndDateTime,A.[Datetime]))) AS diff
--,A.Speed
FROM @tSample AS A
OUTER APPLY
(SELECT TOP(1)
B.[DateTime] AS EndDateTime
FROM @tSample AS B
WHERE
A.[DateTime] < B.[DateTime]
ORDER BY
B.[DateTime] ASC )AS B
WHERE
A.Speed >= 90
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources