0

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)

leonleprofessionnel
  • 15
  • 6

2 Answers2

0

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;
jian
  • 4,119
  • 1
  • 17
  • 32
  • Hey Mark, thank you for your suggestion. But this delivers the same output. traveltime will be assigned to the month in which the pickupdate takes place. if the returndate is one month later there is no share of this period of the time shown in the table. To understand my problem better, please have a look at this question: https://stackoverflow.com/questions/63131585/how-to-determine-the-number-of-days-in-a-month-for-a-given-date-range/71707862#71707862 The difference is, that I don´t have fix boundaries but MANY orderhistories with different dateranges – leonleprofessionnel Apr 01 '22 at 15:30
  • https://dbfiddle.uk/?rdbms=postgres_14&fiddle=6a21696d6270978acfdd4343aae216d0 I computed the intermediate result. Tell me what kind of transformation you want.. – jian Apr 01 '22 at 16:34
  • Hey Mark, this looks brilliant! What I am missing, ist a possibility to assign the days to the referring month (group by them). So I would want the sum of the traveldays to be grouped by the 12 months of the year. In the case of ('2020-02-11', '2020-03-22'), the code must assign 18 days to the sum of february and the other 22 days to the month of march. In the end I want to output the sum of traveldays ordered by the 12 months of the year. In other words a month column and a sum column with 12 rows. Your help is really much appreciated! – leonleprofessionnel Apr 02 '22 at 09:24
  • @leonleprofessionnel Check my updated result. – jian Apr 04 '22 at 04:38
  • I really appreciate your help a lot but it is not the way I want to output it... even though I like your approach. I found another post on the same problem but using Oracle 12c. Maybe you want to have a look, especially on the OUTPUT (https://stackoverflow.com/a/40088011/18493074). In my case instead of projects it would be the different orderHistory id´s that will be shown in the first column. The output shows exactly the way I need it, splitting every order into booking days per month... – leonleprofessionnel Apr 04 '22 at 12:38
  • @leonleprofessionnel I update the answer. use dateranges make things more easier. – jian Apr 04 '22 at 15:09
  • thank you, Mark. Nevertheless it would be very interesting what the equivalent of the answer given here (stackoverflow.com/a/40088011/18493074) to a postresql would look like. I think I have to work with a RECURSIVE CTE right? Because of my reputation I can´t comment on the post under the link given above... (I just started this account) – leonleprofessionnel Apr 04 '22 at 15:43
  • also I found this brilliant solution: https://dba.stackexchange.com/a/292202/250581 BUT it´s written in sql-server and I can´t transform... I feel really stupid rn and I am so sorry for beeing a bloody beginner but the database I am working on is read only so that I can´t alter it... all the problems I had before could be solved with stackoverflow posts after not more than 2 days but this one really got me... any help will be very much appreciated! – leonleprofessionnel Apr 04 '22 at 19:44
  • @leonleprofessionnel if you cannot alter table then use another solution. – jian Apr 05 '22 at 03:53
  • Hey Mark, I hope you didn´t get this wrong, I really enjoyed studying your solution and I think it´s a good way too. Anyhow, I came up with another solution that I posted bellow. Looking farward to your response. Best, Leon – leonleprofessionnel Apr 05 '22 at 08:45
  • @leonleprofessionnel see https://stackoverflow.com/questions/71747571/how-to-split-and-aggregate-days-into-different-month/71748894#71748894 – jian Apr 05 '22 at 11:23
0

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.

leonleprofessionnel
  • 15
  • 6