时间戳

首页 > 解决方案 > 如何将特定值与指定的整数范围匹配？

sql-server - 如何将特定值与指定的整数范围匹配？

问题描述

我有存储贷款时间表的表，请注意我只从时间表表中提取了 1 个 ID

表：时间表

+-------------+-------------+-----------+
|ID           |   Date      | Amount    |
+-------------+-------------+-----------+
| H 1807.0030 | 2020-10-25  | 338850.00 |
| H 1807.0030 | 2020-11-25  | 301200.00 |
| H 1807.0030 | 2020-12-25  | 263550.00 |
| H 1807.0030 | 2021-01-25  | 225900.00 |
| H 1807.0030 | 2021-02-25  | 188250.00 | > Compare2
| H 1807.0030 | 2021-03-25  | 150600.00 | > Compare1
| H 1807.0030 | 2021-04-25  | 112950.00 | > Compare3
| H 1807.0030 | 2021-05-25  | 75300.00  |
| H 1807.0030 | 2021-06-25  | 37650.00  |
+-------------+-------------+-----------+

我想要这样的东西：

第一场比赛：

IF Balance = Schedules.Amount 它应该返回 Date "2021-03-25"

+-------------+-----------+
|ID           | Balance   |
+-------------+-----------+
| H 1807.0030 | 150600.00 |
+-------------+-----------+

比赛2：

IF Balance > Schedules.Amount 且小于 Compare2 它仍应返回 Date "2021-03-25"

+-------------+-----------+
|ID           | Balance   |
+-------------+-----------+
| H 1807.0030 | 150800.00 |
+-------------+-----------+

第 3 场比赛：

如果 Balance < Schedules.Amount 且大于 Compare3，则应返回 Date "2021-04-255"

+-------------+-----------+
|ID           | Balance   |
+-------------+-----------+
| H 1807.0030 | 113950.00 |
+-------------+-----------+

我试过的：

OUTER APPLY (
      SELECT TOP(1) LoanId, DatePayment,InterestBalance, TotalBalance 
      FROM LoanSchedules where LoanID = loans.LoanID
          AND TotalBalance = scheduleofaccountscurrentlr.Balance --##Comparison
      ORDER BY DatePayment DESC
) tmpschedintbal

但它只返回两个匹配金额的日期。有什么建议这样做吗？谢谢。

标签： sql-server

解决方案

如果我理解正确，您想选择Date与Amount等于或小于给定的 对应的Balance。如果没有找到相等Amount的，则必须选择最接近的Amount下面。Balance

样本数据

create table Schedule
(
  ID nvarchar(11),
  Date date,
  Amount money
);

insert into Schedule (ID, Date, Amount) values
('H 1807.0030', '2020-10-25', 338850.00),
('H 1807.0030', '2020-11-25', 301200.00),
('H 1807.0030', '2020-12-25', 263550.00),
('H 1807.0030', '2021-01-25', 225900.00),
('H 1807.0030', '2021-02-25', 188250.00),
('H 1807.0030', '2021-03-25', 150600.00),
('H 1807.0030', '2021-04-25', 112950.00),
('H 1807.0030', '2021-05-25', 75300.00 ),
('H 1807.0030', '2021-06-25', 37650.00 );

create table Ledger
(
  Example int,
  ID nvarchar(11),
  Balance money
);

insert into Ledger (Example, ID, Balance) values
(1, 'H 1807.0030', 150600.00), -- expect 2021-03-25
(2, 'H 1807.0030', 150800.00), -- expect 2021-03-25
(3, 'H 1807.0030', 113950.00); -- expect 2021-04-25

解决方案

select l.Example,
       l.ID,
       l.Balance,
       x.Date
from Ledger l
cross apply (  select top 1 s.Date
               from Schedule s
               where s.ID = l.ID
                 and s.Amount-l.Balance <= 0
               order by s.Amount-l.Balance desc ) x
order by l.Example;

结果

Example  ID           Balance      Date
-------- ------------ ------------ -----------
1        H 1807.0030  150600.0000  2021-03-25
2        H 1807.0030  150800.0000  2021-03-25
3        H 1807.0030  113950.0000  2021-04-25

小提琴

推荐阅读