Update another row value in SQL Server

Question

I have a table with name, location, startdate and enddate as follows:

+------+----------+-----------+-----------+-----------+
| name | location | startdate |  endate   | is_active |
+------+----------+-----------+-----------+-----------+
| A    | delhi    | 3/26/2019 | 3/26/2019 |         1 |
| A    | delhi    | 3/27/2019 | 3/27/2019 |         1 |
| A    | delhi    | 3/28/2019 | 3/28/2019 |         1 |
| A    | delhi    | 3/31/2019 | 3/31/2019 |         1 |
+------+----------+-----------+-----------+-----------+

need to update like this:

+------+----------+-----------+-----------+-----------+
| name | location | startdate |  endate   | is_active |
+------+----------+-----------+-----------+-----------+
| A    | delhi    | 3/26/2019 | 3/28/2019 |         1 |
| A    | delhi    | 3/27/2019 | 3/27/2019 |         0 |
| A    | delhi    | 3/28/2019 | 3/28/2019 |         0 |
| A    | delhi    | 3/31/2019 | 3/31/2019 |         1 |
+------+----------+-----------+-----------+-----------+

If the startdate is consecutive, the update the end date with the end date of last consecutive startdate and also update is_active = 0 for the consecutive startdate

Please try to reformat your sample data so it's a bit more legible. I think that would make it easier to help. — Aaron Hughes, Jul 29 '20 at 14:46

GMB · Accepted Answer · 2020-07-29T14:54:45.340

This is a gaps-and-islands problem. Here is an approach using lag() and a cumulative sum() to define the groups. The final step is conditiona logic:

select 
    name,
    location,
    startdate,
    case when row_number()  over(partition by name, location, grp order by startdate) = 1 
        then max(startdate) over(partition by name, location, grp) 
        else enddate
    end as enddate,
    case when row_number()  over(partition by name, location, grp order by startdate) = 1 
        then 1 
        else 0
    end as is_active
from (
    select 
        t.*, 
        sum(case when startdate = dateadd(day, 1, lag_enddate) then 0 else 1 end) 
            over(partition by name, location order by startdate) grp
    from (
        select 
            t.*, 
            lag(enddate) over(partition by name, location order by startdate) lag_enddate
        from mytable t
    ) t
) t

Demo on DB Fiddle:

name | location | startdate  | enddate    | is_active
:--- | :------- | :--------- | :--------- | --------:
A    | delhi    | 2019-03-26 | 2019-03-28 |         1
A    | delhi    | 2019-03-27 | 2019-03-27 |         0
A    | delhi    | 2019-03-28 | 2019-03-28 |         0
A    | delhi    | 2019-03-31 | 2019-03-31 |         1

Update another row value in SQL Server

1 Answers1