如何加入多对多并保持相同的总额 [英] How to join many to many and keep the same total amount

问题描述

我有两个数据集.左数据集具有相同的QuoteID，PolicyNumber，但可以不同的Year，Month和PaidLosses. 第二个数据集具有不同的QuoteID，相同的PolicyNumber，不同的年份和不同的Month，也可以是多个ClassCode. 我需要将第一个数据集与第二个数据集连接起来，并保持相同的PaidLosses.主要目标是使每个月的总付费损失保持不变.我知道这可能不太适合商业运作，但这就是老板想要看到的.

I have two data-sets. Left data-set has the same QuoteID, PolicyNumber, but can be different Year, Month and PaidLosses. Second data-set has different QuoteID, same PolicyNumber different year, and different Month and also can be multiple ClassCode. I need to join first data-set with second one and keep the same PaidLosses. Main goal is to keep the same total PaidLosses by each month. I know its probably not very business proper, but that's what boss wants to see.

这是我到目前为止尝试过的:

This is what I tried so far:

select      
            cte1.PolicyNumber,
            AccidentYear,
            AccidentMonth,
            cte2.ClassCode,
    /*
        Using ROW_NUMBER() to check if it's the first record in the join and returns
        the PaidLosses value if so, otherwise it will display 0. The ORDER BY (SELECT 0) 
        is there just because I don't need the row number to be based on any explicit 
        order.
    */
            CASE
                WHEN ROW_NUMBER() OVER (PARTITION BY cte1.QuoteID, cte1.PolicyNumber ORDER BY (SELECT 0))=1 THEN cte1.PaidLosses 
                ELSE 0
            END  as PaidLosses
from        cte1 inner join cte2 on cte1.PolicyNumber=cte2.PolicyNumber AND cte1.QuoteID=cte2.QuoteID AND cte1.AccidentYear=cte2.LossYear
            AND cte1.AccidentMonth=cte2.LossMonth

但是由于某种原因，它没有采用某些政策. 理想情况下，我希望看到这样的内容: 在第一行有已支付的损失， 但是如果ClassCode重复相同的Policy，QuoteID，Year和Month，则为0.

But for some reason it doesnt pickup some of the Policies. Ideally I would like to see something like that: Have Paid Losses on the first row, but then If the ClassCode repeats for same Policy, QuoteID, Year and Month then have 0.

推荐答案

我认为您也应该按cte1.AccidentYear, cte1.AccidentMonth进行分区.

I think you should partition also by cte1.AccidentYear, cte1.AccidentMonth.

CASE
WHEN ROW_NUMBER() OVER (PARTITION BY cte1.QuoteID, cte1.PolicyNumbe  cte2.LossYear, cte2.AccidentMonth ORDER BY (SELECT 0))=1 THEN cte1.PaidLosses 
ELSE 0
END  as PaidLosses.

结果将是:

QuoteId PolicyNumber    AccidentYear    AccidentMonth   ClassCode   

PaidLosses
191289  PACA1001776-0   2015    4   50228   26657
191289  PACA1001776-0   2015    4   67228   0
191289  PACA1001776-0   2015    9   50228   16718
191289  PACA1001776-0   2015    9   67228   0
191289  PACA1001776-0   2016    1   50228   3445
191289  PACA1001776-0   2016    1   67228   0

那是你需要的吗?

这篇关于如何加入多对多并保持相同的总额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！