mysql - Select rows matching date bundaries and last 'rent' book before these dates

Question

There are libraries. Every library have plenty of books. And each book may have many statuses (rent, available, unavailable).

I want to get all books that were rent in 2020.

So I have to select all of the books that had status rent with status date between 2020-01-01 and 2021-01-01 AND (and thats the hard part) books that have their last status before 2020-01-01 set to rent.

I have no idea how to achieve that.

---

Libraries:

id	name
1	We have no books
2	We have books

question from:https://stackoverflow.com/questions/65916075/select-rows-matching-date-bundaries-and-last-rent-book-before-these-dates

Answer 1

A simplified answer based solely on status involves capturing the last status in 2019 using a max function and a union for 2020

drop table if exists t;
create table t (id int auto_increment primary key,bid int, status varchar(20), dt date);
insert t(bid,status,dt) values
(3  ,'available',   '2019-01-20'),
(3  ,'rent'    ,  '2019-11-21'),
(3  ,'available',   '2019-11-29'),
(4  ,'available',   '2019-12-01'),
(4  ,'rent'    ,  '2019-12-10'),
(2  ,'available',   '2019-12-20'),
(2  ,'rent'    ,   '2019-12-30'),
(4  ,'available',   '2020-01-02'),
(1  ,'available',   '2020-01-20'),
(1  ,'rent'    ,  '2020-01-22'),
(1  ,'available',   '2020-01-24'),
(2  ,'available',   '2021-01-04'),
(3  ,'rent'    ,  '2021-01-23');

select * from
(
select  t.bid, t.status 
from t
where  (t.status = 'rent' and dt between '2020-01-01' and '2020-12-31') 
limit 1
) a
union  all
select  t.bid ,t.status
from t  
join (select  bid ,max(id) id from t where dt < '2020-01-01' group by bid) s
      on s.bid = t.bid and t.id = s.id 
having status = 'rent'
;

+------+--------+
| bid  | status |
+------+--------+
|    1 | rent   |
|    4 | rent   |
|    2 | rent   |
+------+--------+
3 rows in set (0.022 sec)