Search code examples
sqlsql-serverwindow-functions

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

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:

DER

Solution

  • 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