鋒利的SQL：時間段天數統計

這是在做利息計算時經常遇到的問題。例如，在表1所示的表中記錄着貸款的發放和分期歸還資訊，現在要計算該筆貸款的利息，則應當分期計算。從2009年11月1日至2009年12月1日的貸款額是20000.00元，資金使用時間是30天，這段期間的利息=20000.00×30×日利率。歸還10000.00元後，從2009年12月1日至2010年1月1日的貸款額變為10000.00元，資金使用時間是31天，這段期間的利息=10000.00×31×日利率。現在關鍵的問題是如何計算出兩個日期之間的天數。

表1                                                                      貸款明細表

loan_id	loan_date	summary	dr_amt	cr_amt	bal
1	2009-11-01	發放貸款	20000.00	NULL	20000.00
1	2009-12-01	歸還貸款	NULL	10000.00	10000.00
1	2010-01-01	歸還貸款	NULL	5000.00	5000.00
1	2010-02-01	結清	NULL	5000.00	0.00

首先來建立上面的示例表，代碼如下：

CREATE TABLE Loans

(loan_id int,

 loan_datedate,

 summarychar(10),

 dr_amtdecimal(12,2),

 cr_amtdecimal(12,2),

 baldecimal(12,2));

INSERT INTO Loans

VALUES (1, '2009-11-01', '發放貸款', 20000.00, NULL, 20000.00),

       (1,'2009-12-01', '歸還貸款', NULL,10000.00, 10000.00),

       (1,'2010-01-01', '歸還貸款', NULL,5000.00, 5000.00),

       (1,'2010-02-01', '結清', NULL,5000.00, 0.00);

下面的語句将Loans表打開2次，然後取出大于目前日期的第一個日期，儲存在next_date列中。查詢結果如表2所示。

SELECT loan_id, loan_date,

      (SELECT MIN(loan_date)

        FROMLoans AS L2

       WHERE L2.loan_id = L1.loan_id

         AND L2.loan_date > L1.loan_date) AS next_date,

        bal

FROM Loans AS L1

表2                                                                        查詢結果

loan_id	loan_date	next_date	bal
1	2009-11-01	2009-12-01	20000.00
1	2009-12-01	2010-01-01	10000.00
1	2010-01-01	2010-02-01	5000.00
1	2010-02-01	NULL	0.00

由上表可以看出，next_date-loan_date就可以計算出兩次日期之間的天數。下面是完整的語句：

SELECT loan_id, loan_date,

      DATEDIFF(DAY, loan_date,

                   (SELECT MIN(loan_date)

                     FROM Loans AS L2

                     WHERE L2.loan_id = L1.loan_id

                       AND L2.loan_date >L1.loan_date)) AS diff_days,

        bal

FROM Loans AS L1;