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

Question

Post reply

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 2 years, 7 months ago by gentong.bocor.
- This topic was modified 2 years, 7 months ago by gentong.bocor.
- This topic was modified 2 years, 7 months ago by gentong.bocor.
June 24, 2022 at 6:01 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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: 1656 More actions · Answer 2

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:

Untitled

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.