extract days of daterange grouped by month postresql-CodePudding

I have a pickupDate and returnDate in my OrderHistory table. I want to extract the sum of rental days of all OrderHistory entries, grouped/ordered by month. A cte seems to be the solution but I don´t get how to implement it in my query since the cte´s i saw were refering to themselves where it says "FROM cte".

I tried something like this:

SELECT
   SUM((EXTRACT (DAY FROM("OrderHistory"."returnDate")-("OrderHistory"."pickupDate")))) as traveltime
  , to_char("OrderHistory"."pickupDate"::date, 'YYYY-MM') as M  
  
FROM
  "OrderHistory" 

  GROUP BY 
  M
  
ORDER BY 
  M

But the outcome doesn´t split bookings btw two months (e.g. pickupDate=27th march 2022 and returnDate=03rd of april 2022) but will assign the whole 7 days to the month of march, since the returndate is in it. It should show 4 days in march and 3 in april.

Sorry for the probably very stupid question but I am a beginner. (my code is written in postgresql btw)

CodePudding user response：

PostgreSQL naming conventions

Are PostgreSQL column names case-sensitive?

use legal, lower-case names exclusively so double-quoting is not needed.

Final result in db fiddle

Add daterange column. alter table order_history add column date_ranges daterange; update order_history

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;

then final query:

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;

CodePudding user response：

After trying different things I think I found the best answer to my question, that I want to share with the community:

WITH hier as (
  SELECT
  "OrderHistory"."pickupDate" as start_date
  , "OrderHistory"."returnDate" as end_date                 
  , to_char("OrderHistory"."pickupDate"::date, 'YYYY-MM') as M
  
  
FROM
  "OrderHistory" 
  
  GROUP BY 
  1, 2, 3
  
  
ORDER BY 
  3

), calendar as (
  select date '2022-01-01'   (n || ' days')::interval calendar_date
  from generate_series(0, 365) n
)


select

to_char(calendar_date::date, 'YYYY-MM')
, count(*) as tage_gebucht


from calendar

inner join hier on calendar.calendar_date between start_date and end_date

where calendar_date between '2022-01-01' and '2022-12-31'


group by 1

order by 1;

I think this is the simplest solution I came up with.