Dear All,
I have a requirement in which i need to distribute a value among the rows
For Eg: i have a table having three columns
BillNo , NetAmount , ReceiptAmount
The table will hold the values as below
BillNo NetAmount ReceiptAmount
B1 700 1300
B1 300 1300
B1 200 1300
B1 200 1300
B1 100 1300
I need to add another column called distributed_amount and The results would be as below.
Value in the receiptamount column should be evenly distributed and shown in another column called distributed_amt, based on the balance availability only the distribution should occur. for eg:
at line 4 the available balance for the receiptamount is only 100 so 100 should be allocated
at line 5 there is no balance then the allocation should be 0.
This process should occur billno wise
BillNo NetAmount ReceiptAmount Distributed_Amt
B1 700 1300 700
B1 300 1300 300
B1 200 1300 200
B1 200 1300 100
B1 100 1300 0
Please find the attached screen shot
Any help would be highly appreciated
Thanks
Sushanth.B
February 23, 2020 at 1:31 pm
This is fairly easy to do but is doomed to failure because none of the columns can be used to preserve the order of the rows. There needs to be a transaction DATETIME or some other column that will preserve the temporal order of the distributions.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2020 at 1:39 pm
Dear Jeff,
Thanks for the quick response , Can you please give me a solution assuming there is a unique id column for the above sample data.
In real case there will be a unique id for the result set, i have omitted for the simplicity
Thanks
Sushanth. B
Ok but, in the future, please help us help you by providing the sample data in a readily consumable format. Please see the first link in my signature line for why and how to do such a thing. Here's a slightly different example using your data. Also remember that if something has to be processed or returned in a given order, you need to provide the column(s) to be sorted on, as well.
--=============================================================================
-- Create and populate the test table.
-- This is what we mean by "Readily Consumable Data" and it explains a
-- whole lot about the problem in a single glance.
--=============================================================================
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable
;
CREATE TABLE #TestTable
(
RowNum INT NOT NULL PRIMARY KEY CLUSTERED
,BillNo CHAR(2) NOT NULL
,NetAmount INT NOT NULL
,ReceiptAmount INT NOT NULL
)
;
INSERT INTO #TestTable WITH (TABLOCK)
(RowNum,BillNo,NetAmount,ReceiptAmount)
VALUES (1,'B1',700,1300)
,(2,'B1',300,1300)
,(3,'B1',200,1300)
,(4,'B1',200,1300)
,(5,'B1',100,1300)
;
Once we have such data, it normally won't be long before someone ponies up a solution...
--=============================================================================
-- One of many possible solutions for those using SQL Server 2012 or above.
-- Basically, this is a simple checkbook problem.
--=============================================================================
WITH cteRunningBalance AS
(--==== Create a running balance of how much of the ReceiptAmount is still available
-- after each NetAmount is distributed. This also DRYs out the formulas.
SELECT RowNum,BillNo,NetAmount,ReceiptAmount
,Balance = ReceiptAmount-SUM(NetAmount) OVER (PARTITION BY BillNo ORDER BY RowNum)
FROM #TestTable
)--==== Based on the running balance, distribute the NetAmount from the ReceiptAmount
-- until the ReceiptAmount has been exhaused (like a checking account).
SELECT RowNum,BillNo,NetAmount,ReceiptAmount
,Distributed_Amt = CASE
WHEN Balance > 0 THEN NetAmount
WHEN Balance < 0 AND NetAmount+Balance>=0 THEN NetAmount+Balance
ELSE 0
END
FROM cteRunningBalance bal
ORDER BY BillNo, RowNum
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2020 at 4:57 am
Dear Jeff,
Thanks for the solution, It works except for the small modification at the line
WHEN Balance < 0 AND NetAmount+Balance>=0 THEN NetAmount+Balance
Solution will fail when the receipt amt is changed from 1300 to 1500
so changed the above line to
WHEN Balance <= 0 AND NetAmount+Balance>=0 THEN NetAmount+Balance
Now the solution works perfect for all receipt amt.
Thanks
Sushanth.B
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply