MS SQL Join Two Tables with ‘IN’ Faces Problem

I have two tables for my system.

Table 1: Book

Table 2: BookStatus

Now I want to find out the latest status of books that had been lent from 2018/11/05 to 201/11/10 and how many times they have been lent during this period.

The result I expect is like:

My SQL script is:

SELECT BK.*, 
       BS.status, BS.statusDateTime, 
       (SELECT count(*) FROM BookStatus WHERE status='lent' AND bookId=BK.bookId AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59' ) CountLent 
FROM  Book BK LEFT JOIN BookStatus BS ON BK.bookId = BS.bookId 
WHERE BS.statusId = (SELECT max(statusId) FROM BookStatus WHERE bookId= BK.bookId)
AND BK.bookId IN (SELECT bookId FROM BookStatus WHERE status='lent' AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59')

After I execute the sql query, it just keeps running and doesn’t stop. Is there any problem with my SQL query script? How to revise my SQL script to have the result I expect?

Answer

Find out all the books that were lent during the period and the count using a sub query. Use CROSS APPLY to fetch the latest status:

WITH activity AS (
    SELECT bookId, COUNT(*) AS CountLent
    FROM BookStatus
    WHERE statusDateTime >= '2018-11-05' AND statusDateTime < DATEADD(DAY, 1, '2018-11-10')
    AND status = 'lent'
    GROUP BY bookId
)
SELECT Book.*, activity.CountLent, lastStatus.*
FROM Book
INNER JOIN activity ON Book.bookId = activity.bookId
CROSS APPLY (SELECT TOP 1 status, statusDateTime FROM BookStatus WHERE bookId = Book.bookId ORDER BY statusDateTime DESC) AS lastStatus