Selecting rows with constrained time-stamp differences (in seconds) in tables in SQL

Question

I have a table called visit_times in SQL like this

    name   time_stamp
    Allen  2015-02-13 07:10:54
    Allen  2015-02-13 07:10:58
    Allen  2015-02-13 07:11:02
    Mary   2015-02-17 10:45:33
    Mary   2015-02-17 10:45:39
    Mary   2015-02-17 10:45:43
    ...

I need to select names from "name" column for which all the row-consecutive differences (in second) in the "time_stamp" column equals a certain value. Using the LAG() command I have tried to code it up as follows

WITH cte AS
(
  SELECT  name, 
          DATEDIFF(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap 
  FROM customer_transactions
)
SELECT cte.name
FROM cte
GROUP BY cte.name
HAVING MIN(cte.visit_gap) = 10 AND MAX(cte.visit_gap) = 4;

I expect to get the result as follows:

---------
| name  |
---------
| Allen |
---------

But it outputs nothing! I get the error: in the pre-written template: Incorrect parameter count in the call to native function 'DATEDIFF'

I am not sure how to fix this. Any hints would be appreciated.

It gives you an error or the result is not what you expected? Can you post your expected output and what you get? — Amin Gheibi, Nov 10 '20 at 06:35
@AminGheibi it gives a syntax error (it says near line WHERE visit_gap = 4) while I expect a single name from "name" column to be output. — Fatemeh Talachi, Nov 10 '20 at 06:53
@AminGheibi The error I get says: Unknown column 'visit_gap' in 'where clause' — Fatemeh Talachi, Nov 10 '20 at 07:17
You just made this a lot more confusing. [1] This code does not produce an error, only a warning at most. [2] What is your intention with the grouping? Any data will _never_ have both a minimum of 10 _and_ a maximum of 4... [Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3d2c704c5bc3558b211603846a6deced) with some suggestions. — Sander, Nov 10 '20 at 18:50

score 1 · Accepted Answer · answered Nov 10 '20 at 07:58

SQL queries are processed in a certain order (a quick search for "sql query order of operations" gave me this nice result). The column alias visit_gap can only be reused starting from the order by clause. This explains your syntax error.

The usual solution would be to duplicate the visit_gap expression in the where clause giving you this:

SELECT  name, 
        time_stamp - LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp) AS visit_gap
FROM visit_times
WHERE time_stamp - LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp) = 4;

However, this will give you a new error that states that the LAG() function cannot appear in the where clause...

Windowed functions can only appear in the SELECT or ORDER BY clauses.

In order to separate the LAG() or visit_gap calculation and the filtering (where clause) you could use a common table expression (CTE). Also, use the DATEDIFF() function (function documentation) to calculate the difference between dates.

with cte as
(
  SELECT  name, 
          datediff(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap
  FROM visit_times
)
select cte.name,
       cte.visit_gap --> column alias is available now!
from cte;

Adding a filter in the where clause gives you your final result:

with cte as
(
  SELECT  name, 
          datediff(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap
  FROM visit_times
)
select cte.name,
       cte.visit_gap --> column alias is available now!
from cte
where cte.visit_gap > 4;

Fiddle with all intermediate steps explained!

Thanks so much for your detailed explanation. In using datediff function I get this error: Incorrect parameter count in the call to native function 'DATEDIFF' — Fatemeh Talachi, Nov 10 '20 at 18:25
I tried using time_stamp - LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp) AS visit_gap instead of datediff() function. This would only work on the time differences that do not involve two consecutive minutes, i.e., it cannot capture the qualified time difference in seconds on a case like this 1:59 -> 2:03 which differs by 4 seconds by the minutes are switching — Fatemeh Talachi, Nov 10 '20 at 18:27
Look at [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql) I linked to in my answer. The `datediff()` function takes 3 arguments (multiple examples available in the documentation). Also, the `datediff()` function with argument `second` handles different minutes just fine: [examples](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=67d015af67cb7cae7b0406f1c07e9049&hide=62). — Sander, Nov 10 '20 at 18:40

Selecting rows with constrained time-stamp differences (in seconds) in tables in SQL

1 Answers1