Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

begin;
create table order_history(pickup_date date,   return_date date);
insert into order_history values('2020-03-01', '2020-03-12');
insert into order_history values('2020-03-01', '2020-03-22');
insert into order_history values('2020-03-11', '2020-03-22');
insert into order_history values('2020-02-11', '2020-03-22');
insert into order_history values('2020-01-01', '2020-01-22');
commit;
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select pickup_date,
       return_date,
       return_date - order_history.pickup_date return_date_minue_pickup_date,
        case when
           (EXTRACT(MONTH FROM return_date ) - EXTRACT(MONTH FROM pickup_date)) = 0
                then 'same_month' else 'not same_month' end    same_month,
       case when
           (EXTRACT(MONTH FROM return_date ) - EXTRACT(MONTH FROM pickup_date)) = 0
                then  return_date - pickup_date
                else   ( date_trunc('month', pickup_date) + interval '1 month - 1 day')::date - pickup_date
                end days_pickup_date_same,
       case when
           (EXTRACT(MONTH FROM return_date ) - EXTRACT(MONTH FROM pickup_date)) = 0 then 0
           else  return_date -  date_trunc('month', return_date)::date + 1
            end days_pickup_date_not_same
from order_history;


pickup_date
return_date
return_date_minue_pickup_date
same_month
days_pickup_date_same
days_pickup_date_not_same


2020-03-01
2020-03-12
11
same_month
11
0

2020-03-01
2020-03-22
21
same_month
21
0

2020-03-11
2020-03-22
11
same_month
11
0

2020-02-11
2020-03-22
40
not same_month
18
22

2020-01-01
2020-01-22
21
same_month
21
0


with a as(
select pickup_date,
       return_date,
       to_char( DATE_TRUNC('MONTH', pickup_date),'YYYY-MM') pickup_month,
       to_char( DATE_TRUNC('MONTH', return_date),'YYYY-MM') return_month,
       return_date - order_history.pickup_date return_pickup,
        case when
           (EXTRACT(MONTH FROM return_date ) - EXTRACT(MONTH FROM pickup_date)) = 0
                then 'same_month' else 'not same_month' end    same_month,
       case when
           (EXTRACT(MONTH FROM return_date ) - EXTRACT(MONTH FROM pickup_date)) = 0
                then  return_date - pickup_date
                else   ( date_trunc('month', pickup_date) + interval '1 month - 1 day')::date - pickup_date
                end days_pickup_date_same,
       case when
           (EXTRACT(MONTH FROM return_date ) - EXTRACT(MONTH FROM pickup_date)) = 0 then 0
           else  return_date -  date_trunc('month', return_date)::date + 1
            end days_pickup_date_not_same
from order_history),b as(
select
    pickup_date,
    return_date,
    pickup_month,
    return_month,
    sum(days_pickup_date_same) over(partition by pickup_month) as sum1,
    sum(days_pickup_date_not_same) over (partition by return_month) as sum2
    from a)
select *, case when pickup_month = return_month then sum1 + sum2 else sum1 end as final_total from b;


pickup_date
return_date
pickup_month
return_month
sum1
sum2
final_total


2020-01-01
2020-01-22
2020-01
2020-01
21
0
21

2020-02-11
2020-03-22
2020-02
2020-03
18
22
18

2020-03-01
2020-03-12
2020-03
2020-03
43
22
65

2020-03-01
2020-03-22
2020-03
2020-03
43
22
65

2020-03-11
2020-03-22
2020-03
2020-03
43
22
65


alter table order_history add column date_ranges daterange;
with a(m_begin,  m_end, pickup_date) as
(select date_trunc('month', pickup_date)::date,
 (date_trunc('month', pickup_date) + interval '1 month - 1 day')::date,
        pickup_date from order_history)
update order_history set date_ranges =
    daterange(a.m_begin, a.m_end,'[]') from a
        where a.pickup_date = order_history.pickup_date;
5 rows affected

table order_history;


pickup_date
return_date
date_ranges


2020-01-01
2020-01-22
[2020-01-01,2020-02-01)

2020-02-11
2020-03-22
[2020-02-01,2020-03-01)

2020-03-01
2020-03-12
[2020-03-01,2020-04-01)

2020-03-01
2020-03-22
[2020-03-01,2020-04-01)

2020-03-11
2020-03-22
[2020-03-01,2020-04-01)


WITH A AS(
select
    pickup_date,
    return_date,
    return_date - pickup_date as  total,
case when return_date <@ date_ranges then (return_date  - pickup_date)
else ( date_trunc('month', pickup_date) + interval '1 month - 1 day')::date - pickup_date
end partial_mth
from order_history),
b as (SELECT *, a.total - partial_mth parital_not_mth FROM a)
select *,
case when to_char(pickup_date,'YYYY-MM') = to_char(return_date,'YYYY-MM')
then
sum(partial_mth) over(partition by to_char(pickup_date,'YYYY-MM')) +
sum(parital_not_mth) over (partition by to_char(return_date,'YYYY-MM'))
else sum(partial_mth) over(partition by to_char(pickup_date,'YYYY-MM'))
end
from b;


pickup_date
return_date
total
partial_mth
parital_not_mth
sum


2020-01-01
2020-01-22
21
21
0
21

2020-02-11
2020-03-22
40
18
22
18

2020-03-01
2020-03-12
11
11
0
65

2020-03-01
2020-03-22
21
21
0
65

2020-03-11
2020-03-22
11
11
0
65

pickup_date	return_date	return_date_minue_pickup_date	same_month	days_pickup_date_same	days_pickup_date_not_same
2020-03-01	2020-03-12	11	same_month	11	0
2020-03-01	2020-03-22	21	same_month	21	0
2020-03-11	2020-03-22	11	same_month	11	0
2020-02-11	2020-03-22	40	not same_month	18	22
2020-01-01	2020-01-22	21	same_month	21	0

pickup_date	return_date	pickup_month	return_month	sum1	sum2	final_total
2020-01-01	2020-01-22	2020-01	2020-01	21	0	21
2020-02-11	2020-03-22	2020-02	2020-03	18	22	18
2020-03-01	2020-03-12	2020-03	2020-03	43	22	65
2020-03-01	2020-03-22	2020-03	2020-03	43	22	65
2020-03-11	2020-03-22	2020-03	2020-03	43	22	65

pickup_date	return_date	date_ranges
2020-01-01	2020-01-22	[2020-01-01,2020-02-01)
2020-02-11	2020-03-22	[2020-02-01,2020-03-01)
2020-03-01	2020-03-12	[2020-03-01,2020-04-01)
2020-03-01	2020-03-22	[2020-03-01,2020-04-01)
2020-03-11	2020-03-22	[2020-03-01,2020-04-01)