2

I am looking for assistance to get a running sum of the difference between two dates that are on separate rows.

Table looks like:

client_id name autopay_status contract_id start_date end_date
1 Contract 1 Inactive 1111 "2019-08-30" "2020-02-29"
1 Contract 1 Inactive 1112 "2020-02-29" "2020-08-29"
1 Contract 1 Active 1113 "2020-08-29" "2021-02-28"
2 Contract 2 Inactive 2221 "2019-08-30" "2020-02-29"
2 Contract 2 Inactive 2222 "2020-02-29" "2020-08-29"
2 Contract 2 Active 2223 "2020-08-29" "2021-02-28"
3 Contract 3 Inactive 3331 "2019-08-30" "2020-02-29"
3 Contract 3 Inactive 3332 "2020-03-29" "2020-09-29"
3 Contract 3 Inactive 3333 "2020-09-29" "2021-03-28"
3 Contract 3 Active 3334 "2021-03-28" "2021-09-28"

I have a query that looks at the previous end_date and if it is within one day then that is a continuing contract.

SELECT
    case when
        (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
         else false
    end as continous_contract,
    end_date - start_date as contract_days,
    client_id,
    contract_id,
    autopay_status,
    start_date,
    end_date
FROM
    client_contracts
ORDER BY
    client_id, start_date

This adds two extra alias columns.

continuous_contract contract_days client_id name autopay_status contract_id start_date end_date
true 183 3 Contract 3 Inactive 3331 "2019-08-30" "2020-02-29"
false 184 3 Contract 3 Inactive 3332 "2020-03-29" "2020-09-29"
true 183 3 Contract 3 Inactive 3333 "2020-09-29" "2021-03-28"
true 182 3 Contract 3 Active 3334 "2021-03-28" "2021-09-28"

My goal here is to sum up the days a client has had a continuous contract so table would look similar to below for the above example:

sum_days continuous_contract contract_days client_id
183 true 183 3
184 false 184 3
367 true 183 3
549 true 182 3
733 true 184 3
181 false 181 3

I have put together the below query, but it only sums up the previous two values.

SELECT
*
FROM
(
    SELECT
        *,
        case
            when cc.continuous_contract = true then
                cc.contract_days + coalesce(lag(cc.contract_days) over (partition by cc.client_id), 1)
            else cc.contract_days
        end as added_contract_days
    FROM (
        SELECT
            case when
                (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
                 else false
            end as continuous_contract,
            end_date - start_date as contract_days,
            client_id,
            contract_id,
            autopay_status,
            start_date,
            end_date
        FROM
            client_contracts
        ) as cc
    ) as ccc

I am happy to change anything around to make this work.

Fiddle for assisting in seeing data and structure: Fiddle

0

1 Answer 1

4

Can be done in three steps:

SELECT *, sum(contract_days) OVER (PARTITION BY client_id, contract_nr ORDER BY end_date) AS sum_days
FROM  (
   SELECT *, count(*) FILTER (WHERE NOT continous_contract) OVER (PARTITION BY client_id ORDER BY end_date) AS contract_nr
   FROM  (
      SELECT client_id, start_date, end_date
           , start_date <= lag(end_date, 1, end_date) OVER (PARTITION BY client_id ORDER BY end_date) + 1 AS continous_contract
           , end_date - start_date AS contract_days  -- + 1 ???
      FROM   client_contracts
      ) sub1
   ) sub2
ORDER  BY client_id, start_date;

db<>fiddle here

The inner subquery sub1 is basically what you started with, simplified.
lag() optionally takes 3 arguments, the third being the fallback if no row is found.

sub2 adds a contract_nr for each continuous group of rows: every gap in the contract interval starts a new contract.

The outer SELECT finally adds the running sum.

This assumes that contracts never overlap per client.

See:

Aside: end_date - start_date AS contract_days looks like an off-by-one error? If lower and upper bound shall be included, add + 1. (Of course, overlapping bounds are counted twice then.)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.