CODEWITHSUNDEEP
sqlsql-serversubquerydatediff
tphasley
tphasley

Reputation: 55

Where clause changing the results of my datediff column, How can I work around this?

I'm trying to obtain the time elapsed while st1=5. Here is what I currently have, which gives me the datediff time for each state change. My issue is that when i add a where st1=5 clause the datediff shows the difference in time between instances where the state = 5 instead of time elapsed where state is 5.

select timestamp,st1,st2,st3,st4,
  datediff(second, timestamp, lead(timestamp) 
    over (order by timestamp)) as timediff
from A6K_status
Order By Timestamp DESC


+-----+-----+-----+-----+---------------------+----------+
| st1 | st2 | st3 | st4 | TimeStamp           | TimeDiff |
+-----+-----+-----+-----+---------------------+----------+
| 3   | 3   | 3   | 3   | 2018-07-23 07:51:06 |          |
+-----+-----+-----+-----+---------------------+----------+
| 5   | 5   | 5   | 5   | 2018-07-23 07:50:00 | 66       |
+-----+-----+-----+-----+---------------------+----------+
| 0   | 0   | 10  | 10  | 2018-07-23 07:47:19 | 161      |
+-----+-----+-----+-----+---------------------+----------+
| 5   | 5   | 5   | 5   | 2018-07-23 07:39:07 | 492      |
+-----+-----+-----+-----+---------------------+----------+
| 3   | 3   | 10  | 10  | 2018-07-23 07:37:48 | 79       |
+-----+-----+-----+-----+---------------------+----------+
| 3   | 3   | 10  | 10  | 2018-07-23 07:37:16 | 32       |
+-----+-----+-----+-----+---------------------+----------+

I am trying to sum the time that the state of station1 is 5. From this table above(what I have right now) if i could just sum timediff Where st1=5 that would work perfectly. But by adding "where st1=5" to my query gives me the time difference between instances where the state = 5.

Any help would be much appreciated. I feel very close to the result I would like to achieve. Thanks you.

Edit This is what I would like to achieve

+-----+------------+----------+
| st1 | TimeStamp  | TimeDiff |
+-----+------------+----------+
| 5   | 2018-07-23 | 558      |
+-----+------------+----------+

Upvotes: 1

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You would use a subquery (or CTE):

select sum(timediff)
from (select timestamp, st1, st2, st3, st4,
             datediff(second, timestamp, lead(timestamp) over (order by timestamp)) as timediff
      from A6K_status
     ) s
where st1 = 5;

Upvotes: 1

Laughing Vergil
Laughing Vergil

Reputation: 3756

Assuming SQL Server, try something like this:

WITH SourceTable AS (
select TOP 100 PERCENT timestamp,st1,st2,st3,st4,
  datediff(second, timestamp, lead(timestamp) 
    over (order by timestamp)) as timediff
from A6K_status
Order By Timestamp DESC
)
SELECT SUM(timediff) as totaltimediff
WHERE st1 = 5

Upvotes: 0

Related Questions