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.

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

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

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 :

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) :

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 :

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').

CodePudding user response：

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

CodePudding user response：

It looks like you just need an interval check on the history table, then simply add the group to the group by clause

SELECT
  p.name,
  h.[group],
  [Rent (1 day)] = SUM(CASE WHEN r.type = '10' THEN r.hours END) 
FROM client c 
JOIN history h ON h.user_cid = c.c_id
LEFT JOIN rent r ON r.owner_id = c.c_id
  AND r.date >= h.[from] AND r.date <= h.to
WHERE c.c_id = 'poa'
GROUP BY
  c.name,
  h.[group];

Notes:

• Better not to use reserver keywords such as from or group as object or column names
• A history table should ideally not even have an end time, just calculate the end time by referring to the next row.
• If the dates have time componenets then you need a half-open interval r.date >= h.[from] AND r.date < h.to