CODEWITHSUNDEEP
sqlsql-serverjoin
theedchen
theedchen

Reputation: 2016

MS SQL Join Two Tables with 'IN' Faces Problem

I have two tables for my system.

Table 1: Book

enter image description here

Table 2: BookStatus

enter image description here

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: enter image description here

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?

Upvotes: 1

Views: 142

Answers (4)

Tuấn Đinh
Tuấn Đinh

Reputation: 11

I tried run your code, and it was ok, but result was not true! You can try this:

  SELECT    bd.bookID, bk.bookName, bk.author, 
            statusID, status, statusDateTime ,bc.countLent  
  FROM   ( SELECT    bookID, statusDate = MAX(statusDateTime)
              FROM      bookStatus
              GROUP BY  bookID
         ) AS bd
        LEFT JOIN ( SELECT  bookID, COUNT(*) AS countLent
                    FROM    bookStatus
                    WHERE   [status] = 'lent'
                    GROUP BY bookID
                  ) AS bc ON bc.bookID = bd.bookID
        LEFT JOIN dbo.bookStatus ON bookStatus.bookID = bc.bookID
                                    AND bd.statusDate = dbo.bookStatus.statusDateTime
        LEFT JOIN Book AS bk ON bk.bookID = bookStatus.bookID

The result is good but I know my code is not the best way :D Hope this's help

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272096

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

Upvotes: 1

Bharati Mathapati
Bharati Mathapati

Reputation: 119

Please try your query like below- 

   select b.bookid ,b.bookName , b.Author 
,bs.status ,max(bs.statusDatetime), count(bs.status) Countlent
    from Book b 
    join BookStatus bs on b.bookid= bs.bookid
    where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59'
    and bs.status = 'lent'
    group by b.bookid ,b.bookName , b.Author ,bs.status

Hope this will help you.

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can try below

  select b.bookid ,b.bookName , b.Author ,bs.status ,
    max(bs.statusDatetime) as statusDatetime, count(bs.status) Countlent
    from Book b 
    join BookStatus bs on b.bookid= bs.bookid
    where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59' and bs.status = 'lent'
    group by b.bookid ,b.bookName , b.Author ,bs.status

Upvotes: 0

Related Questions