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

Improve this question
0

1 Answer 1

Reset to default
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.)

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.