选择最近的记录(带过期日期)

rbpvctlc 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(312)

假设我们有两个名为records和opportunities的表：
记录：
RecordId客户创建日期77711/1/202188821/1/202199912/1/2021
机会：
oppidcustomeridoppcreatedate10112/31/20201111/10/20211222/1/20211314/1/20211418/5/2025
期望输出：
RecordId客户创建日期#机会77711/1/2021188821/1/2021199912/1/20211
如您所见，records表提供了所需输出的前3列，“#opportunities”列是通过计算为给定客户创建记录后发生的Opportunity数而创建的。
在这个逻辑中需要注意两个关键点：
仅统计记录后6个月内出现的机会。
如果为客户创建了另一条记录，则仅统计最近记录的商机。
更具体地说，oppid=11将记入recordid=777；12至888；13到999。10和14不会被记入任何一个recordid。
我写了下面的代码，没有考虑上面的#2：

CREATE TABLE #Records
(
RecordID int
, CustomerID int
, CreateDate Date
)

INSERT INTO #Records
VALUES 
(777, 1, '2021-01-01')
, (888, 2, '2021-01-31')
, (999, 1, '2021-02-01')

CREATE TABLE #Opportunities
(
OppID int
, CustomerID int
, OppCreateDate Date
)

INSERT INTO #Opportunities
VALUES 
(10, 1, '2020-12-31')
, (11, 1, '2021-01-10')
, (12, 2, '2021-02-01')
, (13, 1, '2021-04-01')
, (14, 1, '2025-08-25')

select * 
from #Records

select * 
from #Opportunities

select rec.*
, (select count(*)
from #Opportunities opp
where rec.CustomerID=opp.CustomerID
and rec.CreateDate<=opp.OppCreateDate --record happened on the same day or before the opportunity
and datediff(month,rec.CreateDate,opp.OppCreateDate) < 6 --opened and created within 6 months
) as [#Opportunities]
from #Records rec

有什么建议可以合并上面的#2并生成所需的输出吗？

sql Join duplicates sql-server Date

来源：https://stackoverflow.com/questions/67288816/select-most-recent-record-with-expiration-date

2条答案

按热度按时间

aor9mmx11#

决定哪一个 #records 行与 #Opportunities 行基于 #records.CreateDate ```
select RecordID, CustomerID, CreateDate, count(*) cnt
from (
select r.RecordID, r.CustomerID, r.CreateDate,
row_number() over(partition by op.OppID order by r.CreateDate desc) rn
from #records r
join #Opportunities op on r.CustomerID = op.CustomerID and datediff(month, r.CreateDate, op.OppCreateDate) < 6 and r.CreateDate <= op.OppCreateDate
) t
where rn = 1
group by RecordID, CustomerID, CreateDate

退货

RecordID CustomerID CreateDate cnt
777 1 2021-01-01 1
888 2 2021-01-31 1
999 1 2021-02-01 1

赞(0）回复(0）举报 2021-07-26

gt0wga4j2#

试试这个：

DECLARE @Records table ( RecordID int, CustomerID int, CreateDate date );
INSERT INTO @Records VALUES 
    ( 777, 1, '2021-01-01' ), ( 888, 2, '2021-01-31' ), ( 999, 1, '2021-02-01' );

DECLARE @Opportunities table ( OppID int, CustomerID int, OppCreateDate date );
INSERT INTO @Opportunities VALUES 
      ( 10, 1, '2020-12-31' )
    , ( 11, 1, '2021-01-10' )
    , ( 12, 2, '2021-02-01' )
    , ( 13, 1, '2021-04-01' )
    , ( 14, 1, '2025-08-25' );

SELECT
    *
FROM @Records r
OUTER APPLY (

    SELECT
        COUNT ( * ) AS [#Opportunities]
    FROM @Opportunities AS o
    WHERE
        o.CustomerID = r.CustomerID
        AND o.OppCreateDate >= r.CreateDate
        AND DATEDIFF ( month, r.CreateDate, o.OppCreateDate ) <= 6
        AND o.OppID NOT IN (

            SELECT
                OppID
            FROM @Records AS r2 
            INNER JOIN @Opportunities AS o2
                ON r2.CustomerID = o2.CustomerID
            WHERE
                r2.CustomerID = o.CustomerID
                AND o2.OppCreateDate >= r2.CreateDate
                AND r2.RecordID > r.RecordID

        )

) AS Opps
ORDER BY
    r.RecordID;

退货

+----------+------------+------------+----------------+
| RecordID | CustomerID | CreateDate | #Opportunities |
+----------+------------+------------+----------------+
|      777 |          1 | 2021-01-01 |              1 |
|      888 |          2 | 2021-01-31 |              1 |
|      999 |          1 | 2021-02-01 |              1 |
+----------+------------+------------+----------------+

赞(0）回复(0）举报 2021-07-26

我来回答

选择最近的记录(带过期日期)

2条答案

相关问题

热门标签

最新问答