SQL compare row

Question

I'm looking for a stored procedure to compare row by row in a table. For example, I have a table

id | fruit  | farmer |     plant_time      |  status
-------------------------------------------------------
1  | banana | John   | 2021-02-02 18:00:01 |   true
2  | apple  | Steve  | 2021-02-02 18:00:30 |   true   <<<<
3  | apple  | Steve  | 2021-02-02 18:01:10 |   true   <<<<  flag to false
4  | orange | Steve  | 2021-02-02 18:01:50 |   true

I want to find out same fruit same farmer and the difference between plant_time not more than one minute and flag the status to false.

Thanks in advance

Answer 1

I want to find out same fruit same farmer and the difference between plant_time not more than one minute and flag the status to false.

In any supported version of SQL Server, you can use lag() or lead(). As you have expressed the problem:

select t.*,
       (case when lead(planttime) over (partition by farmer order by planntime) < dateadd(minute, 1, planttime)
             then 'false' else 'true'
        end) as status
from t;

If you want to update the value, you can use an updatable CTE:

with toupdate as (
      select t.*,
             (case when lead(planttime) over (partition by farmer order by planntime) < dateadd(minute, 1, planttime)
                   then 'false' else 'true'
              end) as new_status
      from t
     )
update toupdate
    set status = new_status
    where status is null or status <> new_status;

Note you can do this in SQL Server 2008 using apply or a correlated subquery. However, I suspect you are not using unsupported commercial software.

Second, often it is the first in such a sequence that would be flagged rather than the last one. In that case, you would use lag() instead of lead() (and adjust the time comparison).