当前位置: 代码迷 >> SQL >> 犀利的SQL:时间段天数统计
  详细解决方案

犀利的SQL:时间段天数统计

热度:54   发布时间:2016-05-05 12:33:49.0
锋利的SQL:时间段天数统计

这是在做利息计算时经常遇到的问题。例如,在表1所示的表中记录着贷款的发放和分期归还信息,现在要计算该笔贷款的利息,则应当分期计算。从2009111日至2009121日的贷款额是20000.00元,资金使用时间是30天,这段期间的利息=20000.00×30×日利率。归还10000.00元后,从2009121日至201011日的贷款额变为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;

 

  相关解决方案