Please help me to solve this problem.
create table dbo.lib (
lib_id INT,
name varchar(10),
book varchar(10),
flag INT,
Month_year DATE,
event_date DATE
)
INSERT INTO lib
select 1,'C1', 'Book1', 1,'2018-05-01', '2018-05-02' UNION ALL
select 2,'C1', 'Book1', 2,'2018-05-01', '2018-05-04' UNION ALL
select 3,'C2', 'Book2', 2,'2018-05-01', '2018-05-02' UNION ALL
select 4,'C2', 'Book2', 1,'2018-05-01', '2018-05-04' UNION ALL
select 5,'C3', 'Book3', 1,'2018-05-01', '2018-05-02' UNION ALL
select 6,'C3', 'Book3', 2,'2018-05-01', '2018-05-04' UNION ALL
select 7,'C3', 'Book3', 1,'2018-05-01', '2018-05-06' UNION ALL
select 8,'C4', 'Book4', 2,'2018-05-01', '2018-05-02' UNION ALL
select 9,'C4', 'Book4', 1,'2018-05-01', '2018-05-04' UNION ALL
select 10,'C4','Book4', 2,'2018-05-01', '2018-05-06'
1 is rented
2 is returned
i want to result only the white rows and want to suppress the grey ones.
the logic is if the user has first rented and then returned within same month , it will cancel out.
If the user returned and rent again , it should show up .