请教一个利息计算的问题

[zbguolei]

已知各时间段的利率，如何计算现在的利息呢？

[zbguolei]

用sql语句怎么写呢？

[morliz子轩]

先定义一个按日期范围取利率的方法，再将结果 放到计算公式中运算，不就是了。

--demo
--获取计算的存款日期
declare @depositDate date
select @depositDate = [存款日期] from [存款流水表] where xxxxx

declare @rate decimal(10,2) = 0 --利率
select @rate = [利率] from [表名] where @depositDate between [开始日期] and [结束日期]

--将@rate拿过来做运算处理就行了

[锟斤拷锟斤拷]

CREATE TABLE #A (
BDATE DATE,
EDATE DATE,
RATE NUMERIC(3,2)
)
CREATE TABLE #B (
SDATE DATE,
AMOUNT NUMERIC(10,2)
)
INSERT INTO #A VALUES ('2018-01-01','2018-06-30',0.04)
INSERT INTO #A VALUES ('2018-07-01','2018-12-31',0.05)
INSERT INTO #A VALUES ('2019-01-01','2019-12-31',0.07)
INSERT INTO #A VALUES ('2020-01-01','2020-12-31',0.03)

INSERT INTO #B VALUES ('2018-02-28',12345.67)

SELECT B.*,B.AMOUNT*A.RATE INTEREST FROM #B B LEFT JOIN #A A ON B.SDATE BETWEEN A.BDATE AND A.EDATE 

DROP TABLE #A,#B

[Hello World,]

分阶段计算出来再累计就可以了：

CREATE TABLE #A (BeginDate DATE  NOT NULL,EndDate DATE  NOT NULL,Rate NUMERIC(3,2) NOT NULL)
CREATE TABLE #B (StartDate DATE  NOT NULL,Amount NUMERIC(10,2) NOT NULL)
INSERT INTO #A VALUES ('2018-01-01','2018-06-30',0.04)
INSERT INTO #A VALUES ('2018-07-01','2018-12-31',0.05)
INSERT INTO #A VALUES ('2019-01-01','2019-12-31',0.07)
INSERT INTO #A VALUES ('2020-01-01','2021-12-31',0.03)
INSERT INTO #B VALUES ('2018-02-28',12345.67)

;WITH List AS (SELECT   B.StartDate AS BeginDate,
                        CAST(CASE WHEN GETDATE() > A.EndDate THEN A.EndDate ELSE GETDATE()END AS DATE) EndDate,
                        A.Rate,
                        B.Amount,
                        1
                        + DATEDIFF(DAY, B.StartDate, CASE WHEN GETDATE() > A.EndDate THEN A.EndDate ELSE GETDATE()END) AS Days,
                        CAST((1
                              + DATEDIFF(
                                    DAY, B.StartDate, CASE WHEN GETDATE() > A.EndDate THEN A.EndDate ELSE GETDATE()END))
                             * A.Rate / 365 * B.Amount AS MONEY) AS Interest    --如果是复利的话则加上上一阶段的利息
               FROM     #A A
                        INNER JOIN #B B ON A.BeginDate <= B.StartDate AND   A.EndDate >= B.StartDate
               UNION ALL
               SELECT   A.BeginDate,
                        CAST(CASE WHEN GETDATE() > A.EndDate THEN A.EndDate ELSE GETDATE()END AS DATE) EndDate,
                        A.Rate,
                        L.Amount,
                        1
                        + DATEDIFF(DAY, A.BeginDate, CASE WHEN GETDATE() > A.EndDate THEN A.EndDate ELSE GETDATE()END) AS Days,
                        CAST((1
                              + DATEDIFF(
                                    DAY, A.BeginDate, CASE WHEN GETDATE() > A.EndDate THEN A.EndDate ELSE GETDATE()END))
                             * A.Rate / 365 * L.Amount AS MONEY) AS Interest
               FROM     List L
                        INNER JOIN #A A ON DATEADD(DAY, 1, L.EndDate) = A.BeginDate
               WHERE    L.EndDate < GETDATE())
SELECT  * FROM  List;

DROP TABLE #A,#B

[Hello World,]

以上是各阶段的利息，复利的话后一阶段的金额等于上一阶段的金额加上上一阶段的利息，稍改一下就可以

[RINK_1]

你这利率是对应日期区间内的总利率？ 借用#3的数据

CREATE TABLE #A (
BDATE DATE,
EDATE DATE,
RATE NUMERIC(3,2)
)
CREATE TABLE #B (
SDATE DATE,
AMOUNT NUMERIC(10,2)
)
INSERT INTO #A VALUES ('2017-06-01','2017-12-31',0.35)
INSERT INTO #A VALUES ('2018-01-01','2018-06-30',0.04)
INSERT INTO #A VALUES ('2018-07-01','2018-12-31',0.05)
INSERT INTO #A VALUES ('2019-01-01','2019-12-31',0.07)
INSERT INTO #A VALUES ('2020-01-01','2020-12-31',0.03)
INSERT INTO #A VALUES ('2021-01-01','2021-03-31',0.06)
INSERT INTO #A VALUES ('2021-04-01','2021-08-31',0.055)
INSERT INTO #A VALUES ('2021-09-01','2021-12-31',0.038)

INSERT INTO #B VALUES ('2018-02-28',12345.67)


SELECT SUM(AMOUNT*RATE)
FROM #A A
JOIN #B B ON A.EDATE>=B.SDATE AND A.BDATE<=GETDATE()