SQL Server Is there a way to bring the ProcessDate value in First table based on the cumulative total of a column in another table?

qoefvg9y  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(128)

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:

mwg9r5ms

mwg9r5ms1#

See example with recursive query.
Ordering Table2 rows significantly discussed with @Xedni. I will take order by DisposeDate desc as default.

with dn as( -- data with ordering number
select PolicyNo,DisposedDate,ProvinceNo,GWP,Commission_Received
  ,[Pr-Kom],TreatyCompanyCode
  ,row_number()over(partition by TreatyCompanyCode order by DisposedDate desc) rnd
from Table2
)
,r as( -- anchor first row from table1 and first row of table2 with partition
  select PolicyNo,DisposedDate,ProvinceNo,GWP,Commission_Received,[Pr-Kom],rnd  
    ,Payment_total as checkTotal,cast([Pr-Kom] as decimal(20,5)) as currTotal
    ,t1.RowNumber,t1.Payment_Total,t1.ProcessDate,t1.TREATY_COMPANY_CODE,t1.CURRENCY    
  from table1 t1 inner join dn on t1.TREATY_COMPANY_CODE= dn.TREATYCOMPANYCODE
  where t1.rownumber=1 and dn.rnd=1
  union all
  select dn.PolicyNo,dn.DisposedDate,dn.ProvinceNo,dn.GWP,dn.Commission_Received
    ,dn.[Pr-Kom],dn.rnd 
    ,case 
         when cast(r.currTotal as decimal(20,5))> r.checkTotal
            then t1.Payment_total 
     else r.CheckTotal 
     end  as checkTotal
    ,case 
          when cast(r.currTotal as decimal(20,5))> r.checkTotal
            then cast(dn.[Pr-Kom] as decimal(20,5)) 
     else cast(r.currTotal+dn.[Pr-Kom] as decimal(20,5))
     end as currTotal
    ,t1.RowNumber,t1.Payment_Total,t1.ProcessDate,t1.TREATY_COMPANY_CODE,t1.CURRENCY    
  from r 
  inner join dn on r.TREATY_COMPANY_CODE= dn.TREATYCOMPANYCODE
     and dn.rnd=(r.rnd+1)
  inner join table1 t1 on t1.TREATY_COMPANY_CODE= r.TREATY_COMPANY_CODE
    and(case when r.checkTotal>cast(r.currTotal as decimal(20,5)) 
         then r.RowNumber else r.rowNumber+1 end= t1.RowNumber )
)
select * from r

fiddle here

pgpifvop

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.

drop table if exists #T1
create table #T1
(
    RowNumber int,
    ProcessDate date,
    PaymentTotal decimal(20, 2)
)

drop table if exists #T2
create table #T2
(
    RID int,
    PolicyNo int,
    DisposedDate datetime null,
    PrKom decimal(20, 5) null,
    PartitionRunningTotal decimal(20, 5) null,
    GroupId int null, -- Going to update this in a loopdy-loop to join #T1 to later
    ProcessDate date
)
go
insert into #T1
values 
    (1,'2019-12-31',32929.92),
    (2,'2019-11-14',2400.0),
    (3,'2019-10-22',635.0),
    (4,'2019-03-28',-21808.25),
    (5,'2019-02-13',54906.57)

insert into #T2 (RID, PolicyNo, DisposedDate, PrKom)
values
    ( 1, 50620211, N'2019-09-11T00:00:00', 97.64000 ),
    ( 2, 12789054, N'2019-09-11T00:00:00', 27.41000 ),
    ( 3, 12099876, N'2019-09-11T00:00:00', 875.00000 ),
    ( 4, 12125423, N'2019-09-11T00:00:00', 0.00000 ),
    ( 5, 56718901, N'2019-09-11T00:00:00', 1000.05000 ),
    ( 6, 23456791, N'2019-09-11T00:00:00', 1000.05000 ),
    ( 7, 21090323, N'2019-08-27T00:00:00', 1500.00000 ),
    ( 8, 21201921, N'2019-08-23T00:00:00', 775.05000 ),
    ( 9, 45231905, N'2019-08-23T00:00:00', 775.05000 ),
    ( 10, 45129834, N'2019-07-31T00:00:00', 1000.35000 ),
    ( 11, 27819123, N'2019-07-31T00:00:00', 6657.00000 ),
    ( 12, 28917634, N'2019-07-31T00:00:00', 10464.75000 ),
    ( 13, 23179001, N'2019-07-31T00:00:00', 2000.00000 ),
    ( 14, 90030602, N'2019-06-14T00:00:00', 775.05000 ),
    ( 15, 30402213, N'2019-05-13T00:00:00', 774.90000 ),
    ( 16, 34244590, N'2019-04-08T00:00:00', 159.95000 ),
    ( 17, 12893498, N'2019-04-08T00:00:00', 0.00000 ),
    ( 18, 12357634, N'2019-04-08T00:00:00', 775.05000 ),
    ( 19, 19092334, N'2019-04-08T00:00:00', 166.54000 ),
    ( 20, 19003023, N'2019-04-08T00:00:00', 958.77000 ),
    ( 21, 19917823, N'2019-03-26T00:00:00', -8348.95000 ),
    ( 22, 29912365, N'2019-02-21T00:00:00', 415.52000 ),
    ( 23, 76290123, N'2019-02-21T00:00:00', 1584.48000 ),
    ( 24, 90817623, N'2019-02-13T00:00:00', 10246.75000 ),
    ( 25, 23158723, N'2018-12-10T00:00:00', 2000.00000 ),
    ( 26, 23878123, N'2018-08-31T00:00:00', 0.00000 ),
    ( 27, 23198323, N'2018-08-31T00:00:00', 2246.17000 ),
    ( 28, 14712345, N'2018-07-23T00:00:00', 775.05000 )

-- GroupID is synonymous with the RowNumber from #T1. 
declare @GroupId int
select @GroupId = min(RowNumber)
from #T1

declare
    @PaymentTotal decimal(20, 5),
    @ProcessDate date,
    @PartitionEndRID int

-- While there are still rows for which we havn't filled in this data...
while exists
(
    select 1
    from #t2
    where GroupId is null
)
begin

    select
        @PaymentTotal = PaymentTotal,
        @ProcessDate = ProcessDate
    from #t1
    where RowNumber = @GroupId

    -- Get the upper boundary of the current partition
    ;with a as
    (
        select
            RID,
            PartitionRunningTotal = sum(PrKom) over (order by RID)
        from #t2
        where GroupId is null
    )
    select @PartitionEndRID = max(a.RID) + 1 -- +1 to account for the value needing to _exceed_ the Payment Total
    from a
    where PartitionRunningTotal < @PaymentTotal

    -- Update everything in T2 (that's not already set) where the RID is less than the upper boundary
    update #T2
    set GroupId = @GroupId,
        ProcessDate = @ProcessDate
    where GroupId is null
    and RID <= @PartitionEndRID

    -- Go again
    select @GroupID += 1

end

-- This yields the output table
select
    RID,
    PolicyNo,
    DisposedDate,
    PrKom,
    RunningTotal = sum(PrKom) over (order by RID),
    PartitionRunningTotal = sum(PrKom) over (partition by GroupId order by RID),
    GroupId,
    ProcessDate
from #T2
order by RID

I would love to have come up with a more elegant way to do this, but this is the best I got.

相关问题