I have 2 tables:
Table1 Definition:
-- Table 1 Definition
drop table if exists #Table1
create table #Table1
(
TREATY_COMPANY_CODE varchar(3),
CURRENCY varchar(3),
ProcessDate date,
RowNumber int,
Payment_Total decimal(20, 2)
)
insert into #Table1
values
('165', 'USD', '2019-12-31', 1, 32929.92),
('165', 'USD', '2019-11-14', 2, 2400.0),
('165', 'USD', '2019-10-22', 3, 635.0),
('165', 'USD', '2019-03-28', 4, -21808.25),
('165', 'USD', '2019-02-13', 5, 54906.57)
Table2 Definition:
drop table if exists #Table2
create table #Table2
(
PolicyNo int null,
ZeylRankNo int null,
TreatyCompanyCode nvarchar(3) null,
CurrencyType nvarchar(3) null,
DisposedDate datetime null,
ProvinceNo nvarchar(3) null,
GWP decimal(20, 5) null,
Commission_Received decimal(20, 5) null,
PrKom decimal(20, 5) null
)
insert into #Table2
values
('50620211','0','165','USD',43717,'902','146.45','48.81','97.64'),
('12789054','0','165','USD',43717,'902','41.11','13.7','27.41'),
('12099876','0','165','USD',43717,'701','1312.44','437.44','875'),
('12125423','0','165','USD',43717,'701','0','0','0'),
('56718901','0','165','USD',43717,'719','1500','499.95','1000.05'),
('23456791','0','165','USD',43717,'720','1500','499.95','1000.05'),
('21090323','0','165','USD',43702,'720','2000','500','1500'),
('21201921','0','165','USD',43698,'719','1500','724.95','775.05'),
('45231905','0','165','USD',43698,'720','1500','724.95','775.05'),
('45129834','0','165','USD',43675,'719','1500','499.65','1000.35'),
('27819123','0','165','USD',43675,'720','8876','2219','6657'),
('28917634','0','165','USD',43675,'701','13953','3488.25','10464.75'),
('23179001','0','165','USD',43675,'720','2500','500','2000'),
('90030602','0','165','USD',43628,'720','1500','724.95','775.05'),
('30402213','0','165','USD',43596,'720','1500','725.1','774.9'),
('34244590','0','165','USD',43561,'902','262.22','102.27','159.95'),
('12893498','0','165','USD',43561,'701','0','0','0'),
('12357634','0','165','USD',43561,'720','1500','724.95','775.05'),
('19092334','0','165','USD',43561,'902','273.02','106.48','166.54'),
('19003023','0','165','USD',43561,'701','1571.76','612.99','958.77'),
('19917823','1','165','USD',43548,'720','-11029','-2680.05','-8348.95'),
('29912365','0','165','USD',43515,'902','519.4','103.88','415.52'),
('76290123','0','165','USD',43515,'701','1980.6','396.12','1584.48'),
('90817623','0','165','USD',43507,'720','13536','3289.25','10246.75'),
('23158723','0','165','USD',43442,'720','2500','500','2000'),
('23878123','0','165','USD',43341,'701','0','0','0'),
('23198323','0','165','USD',43341,'902','2994.9','748.73','2246.17'),
('14712345','0','165','USD',43302,'720','1500','724.95','775.05')
If cumulative total in [Pr-Kom] Column in Table2 exceeds the 1st number (payment_total:32929,92) in table1, then bring the ProcessDate value to all rows, then move on to next number(2400) , calculate cumulative total and do the same!
This is what I have tried so far:
SELECT
KT.*
,(
CASE WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 1) THEN CAST('2019-12-31' AS date)
WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 2) THEN CAST('2019-11-14' AS date)
WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 3) THEN CAST('2019-10-22' AS date)
WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 4) THEN CAST('2019-03-28' AS date)
WHEN CumulativeTotal <= (SELECT Payment_Total FROM Table1 WHERE RowNumber = 5) THEN CAST('2019-02-13' AS date)
ELSE CAST('1900-01-01' AS date) END
) AS Date
FROM(
SELECT
K.*
,SUM(K.[Pr-Kom]) OVER(ORDER BY K.RN) AS CumulativeTotal
FROM
(SELECT
*,
ROW_NUMBER() OVER(ORDER BY DisposedDate) AS RN
FROM Table2
WHERE TreatyCompanyCode='165'
AND CurrencyType='USD'
) AS K
) AS KT
Expected Result Should be like this:
2条答案
按热度按时间mwg9r5ms1#
See example with recursive query.
Ordering Table2 rows significantly discussed with @Xedni. I will take
order by DisposeDate desc
as default.fiddle here
pgpifvop2#
So this is not a very elegant answer, but it was the best I could come up with.
First, a caveat. Without a deterministic way to order the rows, I don't believe this is possible with SQL. You need to be able to identify where one partition ends, and the other begins, and you simply can't do that if you don't have a way to know which row precedes another.
Perhaps you can alter your data processing mechanism to add a surrogate key or something to enforce order. If that's not possible, then I've got nothing.
With that out of the way, maybe someone can come up with a cleaner answer, but I wasn't able to come up with anything that doesn't involve the dreaded loop.
For every iteration, I'm getting the running total of the remaining records. On the first loop, that's everything, starting at
RID = 1
. On the second loop, that's everything starting after whatever you set in the first pass.From there, I'm getting the max RID plus one to account for the fact that you want the first row that exceeds the threshold from table1.
I would love to have come up with a more elegant way to do this, but this is the best I got.