how to get the result of amount used based on setup range table

• gentong.bocor

  Default port

  Points: 1480

  More actions

  April 25, 2022 at 3:59 am

  #4021567

  assume i have 2 tables

  CREATE TABLE #Setup ([PPHProfileNo] INT, [%DPP] MONEY, RangeFrom MONEY, RangeThru MONEY, [%PPH] MONEY)
  INSERT INTO #Setup VALUES (1,50,0,60000000,5)
  INSERT INTO #Setup VALUES (2,50,60000001,190000000,15)
  INSERT INTO #Setup VALUES (3,50,190000001,250000000,25)
  INSERT INTO #Setup VALUES (4,50,250000001,4500000000,30)
  INSERT INTO #Setup VALUES (5,50,4500000001,999999999999,35)
  
  CREATE TABLE #Transaction (
  [VendorID] [varchar](10) NOT NULL,
  [Year] [int] NOT NULL,
  [SeqNo] [int] NOT NULL,
  [PPHProfileNo] [int] NOT NULL,
  [Amount] [money] NOT NULL,
  [DPP] [money] NOT NULL,
  [RangeFrom] [money] NOT NULL,
  [RangeThru] [money] NOT NULL,
  [%PPH] [money] NOT NULL,
  [Plafon] [money] NOT NULL,
  [Used] [money] NOT NULL,
  [Balance] [money] NOT NULL
  )
  INSERT INTO #Transaction 
  VALUES('ABC01',2022,1,1,200000000.0000,100000000.0000,0.0000,60000000.0000,5.0000,60000000.0000,60000000.0000,0.0000);
  INSERT INTO #Transaction
  VALUES('ABC01',2022,2,2,200000000.0000,100000000.0000,60000001.0000,190000000.0000,15.0000,190000000.0000,40000000.0000,150000000.0000);
  INSERT INTO #Transaction
  VALUES('ABC01',2022,3,2,100000000.0000,50000000.0000,60000001.0000,190000000.0000,15.0000,150000000.0000,50000000.0000,100000000.0000);
  INSERT INTO #Transaction
  VALUES('ABC01',2022,4,2,240000000.0000,120000000.0000,60000001.0000,190000000.0000,15.0000,100000000.0000,100000000.0000,0.0000);
  INSERT INTO #Transaction
  VALUES('ABC01',2022,5,3,240000000.0000,120000000.0000,190000001.0000,250000000.0000,25.0000,250000000.0000,20000000.0000,230000000.0000);
  

  I want to get the result as on #transaction table. For example:

  1. 1st transaction, when we purchase 100M, then it should get the result as on Seqno=1 & 2 on #transaction table, first compare 100M is greater than 60M on PPHProfileNo = 1 from #setup table or not, if greater then we put used = 60M, then the rest we will compare on next PPHProfileNo. Rest amount is 100M-60M=40M, 40M greater than 190M or not, if not then we put used=40M on seqno=2, and balance will be 190M-40M=150M
  2. 2nd transaction, we purchase 50M. then we have to check available balance from #transaction on last seqno, if it's available then we will write a result with same last PPHProfileNo on #transaction then compare 50M with it's balance. if satisfied then used=50M, balance = 150M-50M=100M
  3. 3rd, we purchase 120M, first we check available balance on #transaction on last seqno. if available then put it's last PPHProfileNo then check if 120M > available balance which is 100M. then used become 100M. how about the rest 20M ? we read #setup table on next PPHProfileNo which already used on #transaction. that's PPHProfileNo=3, then we write next result will be PPHProfileNo 3, used=20M because 20M is less than maximum upper limit (RangeThru=250M), balance = 250M-20M=230M

  Then the result you can see is on #transaction table.

  The question is, how if we purchase 250M on next transaction ? The Used will be 230M and balance=0 for PPHProfileNo=3, and next PPHProfileNo=4 which used=20M and balance=450M-20M=450M. Total result will be 2 rows. How the query to get result like this ? Seqno always increased by 1 for next result rows.

  Note: at first query we check for VendorID & Year is already exists in #transaction or not, if not then we can compare from #setup by starting PPHProfileNo=1, otherwise we have to check for available balance on #transaction on last seqno and get it's PPHProfileNo. For example, if new VendorID let's say ABC02 Year=2022 purchase 50M, then the result will be 1 row with PPHProfileNo=1, used=50M, balance 60M-50M=10M. So, as long as new vendor & year, the process start reading from #setup table.

  Sorry for my bad english. Hope you understand what i mean.

  Thanks in advance.

  • This topic was modified 3 years ago by  gentong.bocor.
  • This topic was modified 3 years ago by  gentong.bocor.
  • This topic was modified 3 years ago by  gentong.bocor.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  April 26, 2022 at 4:10 am

  #4022224

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• jschmidt 17654

  SSCommitted

  Points: 1684

  More actions

  May 2, 2022 at 7:30 pm

  #4025635

  In your #Setup, you set some ranges that are rounded to whole dollars with one dollar between ranges, so we have to handle those when using them for calculations. I am guessing that these ranges represent an aggregated sales amount for the year and vendor, essentially "buckets" that get filled by consecutive sales, the first two of which are:

  0 to 60000000

  60000001,190000000

  In your example of the 1st transaction, I see that 60M of the first transaction goes into the first range, taking all 60M, it leaves 40M to go into the second range, which has a total of about 130 M (190M-60000001).

  You show a balance of 190M-40M = 150M, so I'm confused that the balance after 1st transaction wouldn't be 130M - 40M = 90M. (So this 90M would start being used by the 2nd transaction?)

  Is this being done as a batch? So there could be a base table of sales transactions to join with the #setup table? Something like:

  

  I think that using the purchases with a running totals for the start and end of each purchase could be used to find the #Setup ranges where they start and end. Then using the #setup ranges and running totals to calculate the purchases in each range.

   

   

• June 24, 2022 at 6:01 am

  This was removed by the editor as SPAM