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 2005 Forums

Transact-SQL (2005)

Sum times with condition

Author

Topic

orenshto
Starting Member

3 Posts

Posted - 2015-03-10 : 04:36:09

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.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-10 : 11:02:29

Simplistic, but:


declare @ table (_time time, speed int)
insert into @ (_time, speed) values
--DateTime | Speed
('07:50:00', 85),
('08:00:00', 90),
('08:01:00', 92),
('08:03:00', 96),
('08:10:00', 88)

select datediff(second, min(_time), max(_time))
from @
where speed >= 90

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-10 : 11:53:31

[code]-- SwePeso
WITH cteSource(grp, _Time, Speed, Yak)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY s._Time) - ROW_NUMBER() OVER (PARTITION BY SIGN(s.Speed / 90) ORDER BY s._Time) AS grp,
s._Time,
s.Speed,
f._Time AS Yak
FROM @Sample AS s
OUTER APPLY (
SELECT TOP(1) x._Time
FROM @Sample AS x
WHERE x._Time > s._Time
ORDER BY x._Time
) AS f
)
SELECT _Time,
Speed,
CASE
WHEN Speed < 90 THEN NULL
ELSE DATEDIFF(SECOND, _Time, MAX(Yak) OVER (PARTITION BY grp))
END AS SecondsOver90
FROM cteSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources