How to get records where count is above 123 seconds[Answered] RSS

• • Reply
  tvb2727

  Participant

  1038 Points

  2816 Posts

  How to get records where count is above 123 seconds

  Jan 11, 2019 11:37 AM|tvb2727|LINK

  I have a query where it gives me the difference of each row in seconds.

  How can I do a where clause on differenceValue that is above say 125?

   SELECT DateAdd(hour, -6, [EVENT_DT]) as central, DATEDIFF(SECOND ,LAG([EVENT_DT]) OVER(ORDER BY [EVENT_DT]),[EVENT_DT]) as DifferenceValue
  FROM [UTL_EVENT_LOG]
  where SOURCE_DESC_TXT = 'Via_Successful' 
  order by EVENT_DT desc

  DateTime / difference

  2019-01-11 05:33:50.473 == 121
  2019-01-11 05:31:49.357 == 121
  2019-01-11 05:29:48.737 == 120
  2019-01-11 05:27:48.200 == 121

• • Reply
  limno

  All-Star

  123252 Points

  10024 Posts

  Moderator

  Re: How to get records where count is above 123 seconds

  Jan 11, 2019 02:31 PM|limno|LINK

  Select central,DifferenceValue   from (
  SELECT DateAdd(hour, -6, [EVENT_DT]) as central, 
  DATEDIFF(SECOND ,LAG([EVENT_DT]) OVER(ORDER BY [EVENT_DT]),[EVENT_DT]) as DifferenceValue
  FROM [UTL_EVENT_LOG]
  where SOURCE_DESC_TXT = 'Via_Successful' 
  ) t
  WHERE DifferenceValue>123
  order by central desc

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm