时间戳

首页 > 解决方案 > 匹配 SQL 上月底的值

sql - 匹配 SQL 上月底的值

问题描述

我基本上有 2 个表格：Funds 和 Ratings。

create table Ratings
(
 RatingDate date,
 CustomerNumber int,
 CustomerName varchar(50),
 rating varchar(50)
 
);

insert into Ratings (RatingDate,CustomerNumber,CustomerName,Rating) values
( '20200131', 783,'Danny','7+'),
( '20200229', 783,'Danny','5'),
( '20200331', 783,'Danny','5'),
( '20200430', 783,'Danny','1'),
( '20200130', 331,'Isabelle','3'),
( '20200229', 331,'Isabelle','3-'),
( '20200330', 331,'Isabelle','2'),
( '20200430', 331,'Isabelle','2'),
( '20200131', 481,'Denise','5'),
( '20200229', 481,'Denise','5-'),
( '20200331', 481,'Denise','6'),
( '20200430', 481,'Denise','6');

select * from Ratings;

RatingDate | CustomerNumber | CustomerName | rating
:--------- | -------------: | :----------- | :-----
2020-01-31 |            783 | Danny        | 7+    
2020-02-29 |            783 | Danny        | 5     
2020-03-31 |            783 | Danny        | 5     
2020-04-30 |            783 | Danny        | 1     
2020-01-30 |            331 | Isabelle     | 3     
2020-02-29 |            331 | Isabelle     | 3-    
2020-03-30 |            331 | Isabelle     | 2     
2020-04-30 |            331 | Isabelle     | 2     
2020-01-31 |            481 | Denise       | 5     
2020-02-29 |            481 | Denise       | 5-    
2020-03-31 |            481 | Denise       | 6     
2020-04-30 |            481 | Denise       | 6     


create table funds
(
 Date_of_Credit date,
 CustomerNumber int,
 CustomerName varchar(50),
 CreditSuffix int,
 CreditAmount money
);

insert into funds (Date_of_Credit,CustomerNumber,CustomerName,CreditSuffix,CreditAmount) values
('20200204', 783, 'Danny',1000,15000),
('20200309', 331, 'Isabelle',1100,27000),
('20200303', 783, 'Danny',1001,25000),
('20200220', 481, 'Denise',2000,17000)
;

select * from funds;

Date_of_Credit | CustomerNumber | CustomerName | CreditSuffix | CreditAmount
:------------- | -------------: | :----------- | -----------: | -----------:
2020-02-04     |            783 | Danny        |         1000 |   15000.0000
2020-03-09     |            331 | Isabelle     |         1100 |   27000.0000
2020-03-03     |            783 | Danny        |         1001 |   25000.0000
2020-02-20     |            481 | Denise       |         2000 |   17000.0000

DB小提琴

我需要在信用（日期）之前将每个客户的信用交易与上个月的评级相匹配。预期结果如下：

结果

然而，

正如您可以看到我的代码（在链接中），结果与我的预期无关。我究竟做错了什么？

标签： sqlsql-serverdatetimeleft-join

解决方案

假设每个月的最后一天总是有一个评分，您可以使用eomonth()：

select f.*, r.rating
from funds f
left join ratings r 
    on  r.CustomerNumber = f.CustomerNumber
    and r.RatingDate = eomonth(dateadd(month, -1, f.Date_of_Credit))

要使用您的样本数据，您需要将 2020 年 2 月 28 日更改为 2 月 29 日（2020 年是闰年）。

另一方面，如果你想带来上个月的最新评级（不管它的实际日期），那么你可以使用横向连接：

select f.*, r.rating
from funds f
outer apply (
    select top (1) r.*
    from ratings r
    where 
        r.CustomerNumber = f.CustomerNumber
        and r.RatingDate >= dateadd(month, -1, datefromparts(year(f.Date_of_Credit), month(f.Date_of_Credit), 1))
        and r.RatingDate <  datefromparts(year(f.Date_of_Credit), month(f.Date_of_Credit), 1)
    order by r.RatingDate desc
) r

DB Fiddle 上的演示- 两个查询都产生：

Date_of_Credit | 客户编号 | 客户姓名 | 学分后缀 | 信用额度 | 评分
:------------- | -------------: | :----------- | ------------: | ------------: | :-----
2020-02-04 | 第783章 丹尼 | 1000 | 15000.0000 | 7+    
2020-03-09 | 331 | 伊莎贝尔 | 1100 | 27000.0000 | 3-    
2020-03-03 | 第783章 丹尼 | 1001 | 25000.0000 | 5     
2020-02-20 | 481 | 丹妮丝 | 2000 | 17000.0000 | 5

推荐阅读