How can I display an alias column based on a date condition using SQL-CodePudding

I have a library database that I am using. The database contains 5 tables as shown below. I am using MS SQL server management studio

I am currently displaying the bookId, the book name, the author of the book, the book page count, and points from the book table using the following code.

select  
    books.bookId,  
    books.name,  

    authors.surname, 

    types.name, 

    books.pagecount, 
    books.point  
from books 
    join authors 
    on books.authorId = authors.authorId 
    join types 
    on books.typeId = types.typeId

However, I am also required to display a status column of either available or out based on the following logic

IF borrows.takenDate < current_timeStamp 
AND borrows.broughtDate = null
THEN display status = Out (in status alias column)
ELSE display status Available (in status alias column)

This logic has to be added to the SQL statement mentioned prior in the post.

How would I go about doing this in SQL?

Thank you for the assistance :)

CodePudding user response：

Since it looks like borrows is an associative (junction) table between books and students and could have multiple records of taken/returned, you'll want to get the MAX taken date for each unique bookId in a subquery, and join this to your outer query, you can then use a CASE statement to check if the subquery bookId is null, if so, this means the book is Available else it's checked Out:

select  
    books.bookId,  
    books.name,  

    authors.surname, 

    types.name, 

    books.pagecount, 
    books.point
    --Use case statement to see if subquery bookId is null on join
    --if so, it's available else it's checked out
    case when t.bookId is null then 'Available' else 'Out' end as [status]
from books 
join authors 
on books.authorId = authors.authorId 
join types 
on books.typeId = types.typeId 
left join --subquery returns bookId's for all books currently checked out
(
    select bookId
    from borrows
    where takenDate < current_timestamp and broughtDate is null
    group by bookId, takenDate
    having takenDate = max(takenDate)   
) as t
on t.bookId = books.bookId