0

I'm trying to select a query with a subquery that selects the delta between 2 timestamp fields.

For example Delta = Timestamp N - timestamp N-1

select 
    T_event.Actie_ID, actie_omschrijving, event_timestamp, 
    left(custom1,4) as model, custom1, Custom2, 
    (select B.event_timestamp - A.event_timestamp   
     from t_event A, 
          t_event B 
     where B.Event_ID = A.Event_ID-1)
from
    T_event
inner join 
    T_Actie on T_event.Actie_ID = T_Actie.Actie_ID
where
    T_EVENT.Actie_ID in (48, 49, 43, 31, 36) 
    and Event_Timestamp >= '2019-07-22'
order by 
    Event_timestamp desc

This is the error I get:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anthony Lemaire
  • 35
  • 4

3 Answers3

1

You can use the lag() function for this

select T_event.Actie_ID, actie_omschrijving, event_timestamp, left(custom1,4) as model, custom1, Custom2, 
       Lag(T_event.event_timestamp) over (order by T_event.Event_ID) - T_event.event_timestamp AS Delta
from T_event
  inner join T_Actie ON T_event.Actie_ID = T_Actie.Actie_ID
WHERE T_EVENT.Actie_ID in (48,49,43,31,36) 
  and Event_Timestamp >= '2019-07-22'
order by Event_timestamp DESC
Shikhar Arora
  • 886
  • 1
  • 9
  • 14
  • Works like a charm, the only issue i'm experiencing is that it formats the delta as a full date (yyyy-mm-dd hh-mm-ss), rather than just the time (hh-mm-ss). Any way to solve this? – Anthony Lemaire Aug 08 '19 at 10:42
  • if you want only the time part you can cast the result of the computation to time. Reference : https://stackoverflow.com/questions/7710449/how-to-get-time-from-datetime-format-in-sql – Shikhar Arora Aug 08 '19 at 10:48
  • Thanks, additional question tho if i were to calculate the avg value of that delta, how would i have to do it? – Anthony Lemaire Aug 08 '19 at 11:09
  • I'd request you to post a separate question for that, for obvious reasons one thread can pertain to one question – Shikhar Arora Aug 08 '19 at 11:18
1

Actually your inner sub query is not related with the outer query, hence n number of rows as output for each row, which is not acceptable. So possible solution for your query is:

select t.T_event.Actie_ID, t.actie_omschrijving, t.event_timestamp, left(t.custom1,4) as model, t.custom1, t.Custom2, 
       (select B.event_timestamp - A.event_timestamp   
        from t_event A, 
             t_event B 
        where B.Event_ID = A.Event_ID-1 
        and B.Event_ID=t.Event_ID)      ---- here it might be A.event_id.... depends on your input and expected output
from T_event as t
  inner join T_Actie ON t.Actie_ID = T_Actie.Actie_ID
WHERE t.Actie_ID in (48,49,43,31,36) 
  and Event_Timestamp >= '2019-07-22'
order by Event_timestamp DESC
DarkRob
  • 3,843
  • 1
  • 10
  • 27
0

Your subquery returns more than one value. You have to use either TOP clause or apply proper filter condition to make sure that that subquery returns only one row. When you apply TOP make sure that you are applying Proper ORDER BY clause. 

select TOP 1 datediff(day,B.event_timestamp, A.event_timestamp) AS timediff
        from t_event A, 
             t_event B 
        where B.Event_ID = A.Event_ID-1
        ORDER BY timediff

Another thing, what I find from your query is, the timestamp difference you calculate, is not related to outer query. You have to use, outer query EventID

select T_event.Actie_ID, actie_omschrijving, event_timestamp, left(custom1,4) as model, custom1, Custom2, 
       (select TOP 1 datediff(day,B.event_timestamp, outer.event_timestamp) as timediff 
        from t_event AS B 
        where outer.Event_ID = A.Event_ID-1
        ORDER BY timediff) as timestampdifference
from T_event as outer
  inner join T_Actie ON T_event.Actie_ID = T_Actie.Actie_ID
WHERE T_EVENT.Actie_ID in (48,49,43,31,36) 
  and Event_Timestamp >= '2019-07-22'
order by Event_timestamp DESC
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58