Distributing a value among the rows in Sql Server

Question

Post reply

Distributing a value among the rows in Sql Server

bsushu

SSC Enthusiast

Points: 176

February 23, 2020 at 1:17 pm

Go to Answer

#3728003

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

Attachments:

You must be logged in to view attached files.

Jeff Moden

SSC Guru

Points: 1004591

February 23, 2020 at 2:47 pm

Answer

#3728010

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bsushu SSC Enthusiast Points: 176 More actions · Answer 2

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

bsushu SSC Enthusiast Points: 176 More actions · Answer 3

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

Distributing a value among the rows in Sql Server

Attachments:

Cookies on SQLServerCentral