0

who can help me with my query?

here's the screenshot of the table.

enter image description here

i want to compute the range/minutes between two dates. My idea is to subtract the second row of FORMATTED_DATEMODIFIED to first row of FORMATTED_DATEMODIFIED and so on. i want to subtract the date from previous row.

anyone can help? thank you very much!!

aiipee
  • 252
  • 3
  • 9
  • 18
  • The screenshot is not sufficient. You need to provide at least the datatype information. But IIRC, FireBird out-of-the-box is pretty standard SQL 92, and it does not provide any means to evaluate expressions over previous rows of a result set (like MS-SQL Server or other engines). – Frazz Jul 23 '14 at 08:11
  • TESTERID(VARCHAR), FALSE_STATUS(VARCHAR) and FORMATTED_DATEMODIFIED(DATE) – aiipee Jul 23 '14 at 08:14
  • With Firebird 2.5 and earlier this isn't very easy; Firebird 3.0 will add Window functions, but it will be a while before it gets released. – Mark Rotteveel Jul 23 '14 at 09:12

1 Answers1

0

Maybe something like this will help you

SELECT
CAST (
( SELECT FIRST 1 TT2.FORMATTED_DATEMODIFIED
  FROM TEST_TABLE TT2
  WHERE TT2.FORMATTED_DATEMODIFIED > TT.FORMATTED_DATEMODIFIED
  ORDER BY TT.FORMATTED_DATEMODIFIED) AS TIME)
  - CAST(TT.FORMATTED_DATEMODIFIED AS TIME) AS DIFF_IN_SECONDS,

  TT.FORMATTED_DATEMODIFIED AS FOUND_TIMESTAMP,

  ( SELECT FIRST 1 TT2.FORMATTED_DATEMODIFIED
  FROM TEST_TABLE TT2
  WHERE TT2.FORMATTED_DATEMODIFIED > TT.FORMATTED_DATEMODIFIED
  ORDER BY TT.FORMATTED_DATEMODIFIED) AS NEXT_TIMESTAMP

FROM TEST_TABLE TT
ORDER BY TT.FORMATTED_DATEMODIFIED

/Jesper H.

Jesper
  • 1