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
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.