how to add values of related rows to a column in SQL

user_15212463 0
Viewed 138

I have a table with contract information and I would like to add a calculated column that identifies when a contract is consecutive to the previous one of the same client. So when the end date of a contract matches the start date of the following one for the same client we consider that is consecutive.

The data looks like this:

enter image description here

And I would like it to look like this:

enter image description here

I tried doing an inner join of the contract table with itself and then I unioned it, but I don't think that is the most effective way of doing it.

Do you know of a better way of achieving this?

Thanks in advance.

trino presto sql
2 Answers

The code below was tested on SQL Server, but may also work on Presto. SQL Server has bit values rather than booleans, so I've returned TRUE and FALSE as strings. Hopefully you'll be able to make any necessary modifications.

SELECT
  c.contract_id
  ,c.client
  ,c.start_date
  ,c.end_date
  ,CASE
       WHEN LAG(c.end_date) OVER (PARTITION BY c.client ORDER BY c.start_date) = c.start_date
       THEN 'TRUE' ELSE 'FALSE'
   END as is_consecutive
  ,CASE
       WHEN LAG(c.end_date) OVER (PARTITION BY c.client ORDER BY c.start_date) = c.start_date
       THEN LAG(c.contract_id) OVER (PARTITION BY c.client ORDER BY c.start_date)
   END as related_previous
  ,CASE
       WHEN LEAD(c.start_date) OVER (PARTITION BY c.client ORDER BY c.start_date) = c.end_date
       THEN LEAD(c.contract_id) OVER (PARTITION BY c.client ORDER BY c.start_date)
   END as related_next
FROM
  contract c
user_37514200

Your "related ids" is a challenge if there are more than 2. That turns this into a gaps-and-islands problem, best approach using lag() and a cumulative sum:

select t.*,
       coalesce(prev_end_date = end_date, false) as is_consecutive,
       array_agg(contract_id) over (partition by clent, grp order by start_date) as contracts
from (select sum(case when prev_end_date = end_date then 0 else 1 end) over (partition by client order by start_date) as grp
      from (select t.*,
                   lag(end_date) over (partition by client order by start_date) as prev_end_date
            from t
           ) t
     ) t;

Note: This puts all related contracts into an array. You can remove the current contract, if you like.

user_11440350
Related