添加一个列，根据日期[借来的日期]给出否或是

Question

1. 基本上是我想要的列，它将出现在借来日期
2. 旁边，这个列将被称为状态，它将有两个值
3. ，值将取决于这样一个条件:如果今天的日期比借来的日期大3天，则状态栏=过期，否则应该显示未过期的

。

这是我的密码。

SELECT dbo.borrowing.[book_id], 
       dbo.bookregistration.[book_title], 
       dbo.bookregistration.[book_category], 
       dbo.bookregistration.[book_type], 
       dbo.bookregistration.edition, 
       dbo.borrowing.[borrowed_date], 
       dbo.borrowing.[adm_no] 
FROM   dbo.bookregistration 
INNER JOIN dbo.borrowing ON dbo.bookregistration.[book_id] = dbo.borrowing.[book_id] 

Answer 1

我们开始：

SELECT dbo.Borrowing.[Book_ID], dbo.BookRegistration.[Book_Title]
    , dbo.BookRegistration.[Book_Category], dbo.BookRegistration.[Book_Type]
    , dbo.BookRegistration.Edition, dbo.Borrowing.[Borrowed_Date] AS [Borrowed_Date]
    , case when DATEADD(DD,3,[Borrowed_Date]) > GETDATE() THEN 'Not Overdue' ELSE 'Overdue' END AS Status
    , dbo.Borrowing.[Adm_NO]
FROM dbo.BookRegistration
INNER JOIN dbo.Borrowing ON dbo.BookRegistration.[Book_ID] = dbo.Borrowing.[Book_ID]

Answer 2

这是你的问题。First我们将用alias缩短它，使用select case使第二更短

，这是您的完整查询.

SELECT t2.[Book_ID]
    , t1.[Book_Title]
    , t1.[Book_Category]
    , t1.[Book_Type]
    , t1.Edition
    , case when t2.[Borrowed_Date] < getdate() - 3 then 'OverDue' else 'Not Overdue' end as [Status]
    , t2.[Adm_NO]
FROM  dbo.BookRegistration t1 
INNER JOIN dbo.Borrowing t2 ON t1.[Book_ID] = t2.[Book_ID]

Answer 3

您可以使用DATE_ADD和CASE语句来实现如下所示的需求-

SELECT        
    CASE 
        WHEN DATE_ADD([Borrowed Date], INTERVAL 3 DAY) > NOW() THEN 'Not Overdue'
        ELSE 'Overdue'
    END Status
FROM dbo.BookRegistration
INNER JOIN dbo.Borrowing 
    ON dbo.BookRegistration.[Book_ID] = dbo.Borrowing.[Book_ID]