April 21, 2022 at 7:14 am
CREATE TABLE #Tamp (SeqNo INT, [%DPP] MONEY, RangeFrom MONEY, RangeThru MONEY, [%PPH] MONEY)
INSERT INTO #Tamp VALUES (1,50,0,60000000,5)
INSERT INTO #Tamp VALUES (2,50,60000001,190000000,15)
INSERT INTO #Tamp VALUES (3,50,190000001,250000000,25)
INSERT INTO #Tamp VALUES (4,50,250000001,4500000000,30)
INSERT INTO #Tamp VALUES (5,50,4500000001,999999999999,35)
if i put 55000000, i want the result like this
SeqNo Used
1 60000000.00
2 190000000.00
3 300000000.00
if i put 50000000
SeqNo Used
1 50000000.00
Thanks
April 21, 2022 at 10:37 am
SeqNo Used
1 60000000.00
2 190000000.00
3 250000000.00
4 50000000.00
Not sure how good the solution is but the thing that first came to my mind was using the running total
--#############################################################
--Get Values range through from leftover until input is 0
--#############################################################
--CALC Running Total to find out max SeQNo required
DECLARE @SEARCH_VARIABLE INT = 550000000
--Get all relevant entries below the final one
select SeqNo
,RangeThru
--,Running_Total
from (
select *
,Running_Total = SUM(RangeThru) OVER (ORDER BY SeqNo)
from #Tamp
) with_rt
where Running_Total < @SEARCH_VARIABLE
UNION ALL
--Get Leftover of the final one
select *
--One more () because of the required ORDER BY
from (--Last entry of previous results and add + 1 sequence + calculate the leftover
select top 1
SeqNo = SeqNo + 1
,LEFTOVER = @SEARCH_VARIABLE - Running_Total
from (
select *
,Running_Total = SUM(RangeThru) OVER (ORDER BY SeqNo)
from #Tamp
) with_rt
where Running_Total < @SEARCH_VARIABLE
ORDER BY Running_Total desc
) ordered_result
I want to be the very best
Like no one ever was
April 24, 2022 at 10:46 pm
i mean if i put 55 millions, then you should give the output of 3 rows where sum all used columns is 55 millions. used column is based on the range table. if still satisfied within the range then used will be the upper limit. if not then used will be the rest amount where it already used before rows. sorry for my bad english. hope u understand. thanks..
April 25, 2022 at 4:39 am
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:
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
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
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.
July 29, 2022 at 6:07 am
This was removed by the editor as SPAM
July 29, 2022 at 6:07 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply