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

  • 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
    INSERT INTO #Transaction
    INSERT INTO #Transaction
    INSERT INTO #Transaction
    INSERT INTO #Transaction

    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.

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

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

  • 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


    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.



  • 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