Why can’t I sum values ​for a given period with intervals present in another table?

I’m using a software. I can not change the model.

The software is a film rental manager.

In the database, I have some clients and differents type of rental and different type of client (free, vip, vip+, …)

A client can rent a movie for one day, one week, buy a movie, etc. He starts as a free client and if he pay, he can get VIP or VIP+ offer.

Inside this table (Rent), I have the information about a rent. I know which type of rent it is (10 is for rent only for one day), the date and the duration of the movie rental.

owner_id	Type	Date	hours
POA	10	2021-01-28	8
POA	10	2021-06-29	7.30

POA is the owner_id. It’s a natural id. It comes from my client table :

id	c_id	Name
123	POA	Paul

And I have a table that contains all the offer’s history for one client.

user_cid	group	from	to
poa	free	2000-01-01	2021-04-30
poa	VIP	2021-05-01	2021-06-30
poa	VIP+	2021-07-01	2099-12-31

2000-01-01 and 2099-12-31 are default value given by the software. I can’t change them.

2000-01-01 is the default value when a user is created and 2099-12-31 means that this offer is the current client’s offer.

In this case, Paul was a free member from the day he created his account to the 2021-04-30 and then he became a VIP member from the 2021-05-01 to the 2021-06-30. After this, he subscribed to a VIP+ offer from the 2021-07-01 and it’s still his current offer to this day.

Now what I’m trying to do, is to display a history of films rented by Paul in a given period. But I would like to separate the history by group.

Per example If I give a period from 2021-01-01 to 2021-06-30, a desired input would be this :

Name	Group	Rent (1 day)
Paul	free	8
Paul	VIP	7.30

I can see the total duration of the type 10 rentals (which is one-day rental) for each group. When Paul was a free user he rented films for 8 hours and when he was a VIP user he rented for 7.30 hours (as we can see in the first and the third table).

So I tried this :

SELECT p.name, h.group, sum(caase when r.type = '10' then r.hours else 0 end) 

FROM client p 
left outer join history h
 on h.user_cid = p.c_id
join rent r 
 on r.owner_id = p.c_id
and r.date between '2021-01-01' and '2021-06-30'

where p.c_id = 'poa'

group by p.name

With this request I have this result (it sums all the hours and not by group) :

Name	Group	Rent (1 day)
Paul	free	15.30
Paul	VIP	15.30

So I don’t know how to handle the history date.

I have to handle it like this :

If the start period is 2021-01-01 and the end period is 2021-06-30 then sum the duration from the start period to the end of the free offer 2021-04-30 and then in an other row sum the duration from the start of the vip offer 2021-05-01 to the end period. (And repeat this pattern if the member changed his subscription more than 2 time).

Finally, I have to handle the default date 2000-01-01 and 2099-12-31 too. I thought about doing something like this :

caase when from '2000-01-01' then `2021-01-01` else r.date end

PS :

Why can’t I post my question when I write ‘case’ inside a code block ? I had to write it with two ‘a’ to post it.

UPDATE

Result of ekochergin’s soluion :

Name	Group	Rent (1 day)
Paul	free	7.30
Paul	VIP	0

It didn’t sum correctly. 7.30 is supposed to be in the VIP group and VIP is 0 (the result should be 7.30').

UPDATE 2

So here is my full table :

user_cid	item_id	type	group	from	to
poa	c_gr1	group	free	2000-01-01	2021-04-30
poa	c_gr2	group	VIP	2021-05-01	2021-06-30
poa	c_gr3	group	VIP+	2021-07-01	2099-12-31
poa	c_tp1	plateform	android	2000-01-01	2021-02-03
poa	c_tp2	plateform	webos	2021-02-04	2099-12-31
poa	c_cntry1	country	uk	2000-01-01	2021-03-01
poa	c_cntry2	country	usa	2021-03-02	2099-12-31

For each type I have a table that display some basics information

group_id	offer	price
c_grp1	free	0
c_grp2	VIP	5
c_grp3	VIP+	10

plateform_id	name	brand
c_tp1	android	google
c_tp2	webos	LG

country_id	name	capital_city
c_cntry1	uk	London
c_cntry2	USA	Washington

Here is my request :

SELECT p.name, grp.name, sum(caase when r.type = '10' then r.hours else 0 end) 

FROM client p 
left outer join userHistory ch
 on ch.user_cid = p.c_id
join country ctry
 on ctry.country_id = ch.item_id
and ch.type='country'
and ch.fto >='2021-01-01' and ch.from <='2021-06-30'

left outer join userHistory ph
 on ph.user_cid = p.c_id
join plateform pl
 on pl.plateform_id = ph.item_id
and ph.type='plateform'
and ph.to >='2021-01-01' and ph.from <='2021-06-30'

left outer join userHistory gh
 on gh.user_cid = p.c_id
join group g
 on g.plateform_id = gh.item_id
and gh.type='group'
and gh.to >='2021-01-01' and gh.from <='2021-06-30'

join rent r 
 on r.owner_id = p.c_id
   and r.date between gh.date_from and gh.date_to
 where p.c_id = 'poa'
   and r.date between '2021-01-01' and '2021-06-30'
group by
 p.name,
 grp.name,
 r.type,
 r.hours

Answer

as I understood the question, all you need is to show for how many hours a user rented something divided by user’s statuses (free, VIP, VIP+)

If so, you’re almost there. I believe the problem is in “r.date between” condition.

when I changed it to

and r.date between h.date_from and h.date_to

the query gave me answer which seems to be correct.

PS. Not sure if you need to use left join here. As far as I can understand the logic, I think a regular join suits better

UPD: would this help?

SELECT p.name, 
       h.group_name, 
       sum(case when r.type = '10' then r.hours else 0 end) 
  FROM client p 
  left join history h
    on h.user_cid = p.c_id
  join rent r 
    on r.owner_id = p.c_id
   and r.date between h.date_from and h.date_to
 where p.c_id = 'poa'
   and r.date between '2021-01-01' and '2021-06-30'
 group by p.name, h.group_name

UPD2: Are you sure you have tested on same data as in the question? On my test system it gave exactly result you needed

UPD3: here, check the result with some additional test data Note for simplicity reason I have commented date limitation clause